Concatenation Problem / VBA ?

[carl]

I've been working on this problem for a while. My data table looks like this
(the actual table has approximately 5000 rows and for a given ID/Engine
Combo, can have as many as 300 codes that need to be concatenated.

ID Code Engine
BOX050 W3 1
BOX050 F3 1
BOX050 Y0 1
BOX050 A8 1
BOX050 B9 1
BOX050 K0 1
BOX050 K2 1
BOX050 T1 1
BOX050 X2 1
BOX050 AP 1
BOX050 D5 1
BOX050 F6 1
BOX050 G9 1
BOX050 GE 1
BOX050 K3 1
BOX050 L3 1
BOX050 MF 1
BOX050 N7 1
BOX050 N6 1
BOX050 U3 1
BOX050 AK 1
BOX050 BD 1
BOX050 BH 1


I am trying to create a table like this:

Partial Example of Table Trying To Create

BOX050 1 W3;F3;Y0;A8;B9;K0;K2;;.....etc
BOX050 2 E7;R0;R3;C6;Q2;A1;AJ;A3;...etc
BOX060 1 AN;G0;C2;E1;A6;B5;BE;K0;...etc
BOX060 2 M0;A7;B0;EA;L1;M2;U5;X3;...etc
BOX355 1 AN;C2;E1;I0;A6;B5;BE;J0;K0;...etc
BOX355 2 I1;M0;Q1;B0;D9;EA;K1;L1;...etc
BOX549 1 etc
etc 1 etc
etc 2 etc

Thank you in advance for all ideas on how to accomplish this.

 

[Dave Peterson]

Do this against a copy of your worksheet--it destroys when it merges!

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

It assumes that your data is in columns A:C (and no place else). It sorts the
data by column A, then column C, then column B (that third key is
optional--remove it if you don't want that column sorted).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[carl]

Thank you Dave. I ran this code and it kind of looped (basically looked like
it tried to keep sorting).

That said, the table I am trying to build (above) has the codes concatenated
with a semicolon separator.

Was your code intended to perfprm the concatenation ?

Thanks Again.

 

[Dave Peterson]

Yep. It worked fine for me.

Was your data laid out in columns A:C?

 

[carl]

Thank you. This worked very well.

Yep. It worked fine for me.

Was your data laid out in columns A:C?

 

[Harlan Grove]

carl wrote...

I've been working on this problem for a while. My data table looks like this
(the actual table has approximately 5000 rows and for a given ID/Engine
Combo, can have as many as 300 codes that need to be concatenated.

ID Code Engine
BOX050 W3 1
BOX050 F3 1
BOX050 Y0 1
BOX050 A8 1
BOX050 B9 1
BOX050 K0 1
BOX050 K2 1
BOX050 T1 1
BOX050 X2 1
BOX050 AP 1
BOX050 D5 1
BOX050 F6 1
BOX050 G9 1
BOX050 GE 1
BOX050 K3 1
BOX050 L3 1
BOX050 MF 1
BOX050 N7 1
BOX050 N6 1
BOX050 U3 1
BOX050 AK 1
BOX050 BD 1
BOX050 BH 1


I am trying to create a table like this:

Partial Example of Table Trying To Create

BOX050 1 W3;F3;Y0;A8;B9;K0;K2;;.....etc
BOX050 2 E7;R0;R3;C6;Q2;A1;AJ;A3;...etc
BOX060 1 AN;G0;C2;E1;A6;B5;BE;K0;...etc
BOX060 2 M0;A7;B0;EA;L1;M2;U5;X3;...etc
BOX355 1 AN;C2;E1;I0;A6;B5;BE;J0;K0;...etc
BOX355 2 I1;M0;Q1;B0;D9;EA;K1;L1;...etc
BOX549 1 etc

....

As an alternative that would allow you to use formulas, try the
user-defined function mcat from

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94456a9e326b19a6

(or http://makeashorterlink.com/?S1E33459B ). If your original table
had field names in the top row and were in, say, A1:C5000, enter ID and
Engine in, say, X1 and Y1, respectively. Select the original table and
run Data > Filter > Advanced Filter, opting to 'Copy to another
location', leave the Criteria range entry blank but enter the range
containing ID and Engine (X1:Y1) in the Copy to entry field, check
Unique records only, and click OK. That should generate a listing of
the distinct combinations of ID and Engine from the original table.
Then create the concatenated field you want using array formulas like

Z2 [array formula]:
=REPLACE(MCAT(IF((A$2:A$5000=X2)*(C$2:C5000=Y2),";"&B$2:B$5000,"")),1,1,"")

Fill Z2 down as far as needed (or just double click on the fill handle).