ExcelBanter - Macro to combine 'like' rows

Here's an alternative formulas driven model which also delivers the goods
dynamically

In Sheet1,
Source data as posted is assumed in A2:B2 down
Place
In C2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))
In D2: =INDEX(A:A,SMALL(C:C,ROWS($1:1)))
In E2: =IF($A2=INDEX($D:$D,COLUMNS($A:A)+1),ROW(),"")
Copy E2 across by say, 5 cols to I2 (copy E2 across by as many cols to cover
the max expected # of repeats per any unique value in col A's data). Select
C2:I2, fill down as far as required to cover the max expected extent of
source data, say down to I100. Leave C1:I1 empty. Minimize/Hide away cols C
to I.

Then in another sheet,
To draw out the results dynamically:
In A2: =IF(ISERROR(Sheet1!D2),"",Sheet1!D2)
In B2:
=IF(COLUMNS($A:A)COUNT(OFFSET(Sheet1!$D:$D,,ROWS( $1:1))),"",INDEX(Sheet1!$B:$B,SMALL(OFFSET(Sheet1! $D:$D,,ROWS($1:1)),COLUMNS($A:A))))
Copy B1 across by 5 cols to F1 (the same extent as done for E2 in Sheet1).
Select A1:F1, fill down to F100 (cover the same extent as done in Sheet1).
Cols A to F will return the exact results that you seek.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---
wrote in message
...
I have data where the first column has like data and the second column
has unique data such as below:

1234 BBB
1234 ZZZ
9999 ALL
5431 JJJ
5431 LLL

I've been trying to figure out a macro or anything really to combine
the like rows and delete the old ones. For example the ouput would
look like this:

1234 BBB ZZZ
9999 ALL
5431 JJJ LLL

Thanks!

- Tek