Macro to combine 'like' rows

  • Thread starter Thread starter tekari
  • Start date Start date
T

tekari

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
 
You have a response at your other post.

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
Click to expand...
 
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
 
Back
Top