Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to combine 'like' rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to combine 'like' rows
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine details on rows | Excel Discussion (Misc queries) | |||
Combine simmilar rows | Excel Discussion (Misc queries) | |||
combine 2 rows into 1 record | Excel Discussion (Misc queries) | |||
Combine rows | Excel Discussion (Misc queries) | |||
how to combine the multiple rows into one rows? | Excel Worksheet Functions |