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/micro...456a9e326b19a6

(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).