Aligning Like Data in Table Columns

[beadedbytes]

I received a data file containing records that look like the following. Column 3 (C3) contains 1-digit codes that describe something about the person (Name). For example, "E" might indicate that the person wants communication by email.

C1[tab]C2[tab] C3
ID1[tab]Name1[tab]A:B:C:E
ID2[tab]Name2[tab]D:E:B:C
ID3[tab]Name3[tab]D:A:B
ID4[tab]Name4[tab]E:C:A


The 1-digit codes can be in a different order for every record. Furthermore, while one record might have A-B-C-D-E, another record may only have A-D-E.

I want to place all 'like' 1-digit codes in the same column for this post, is there a way to sort this information after it's been parsed into separate columns? (NOTE: On the chart below, 'x'=no value in field. In an effort to align the field values in columns, I used '---').

C1 C2 C3 C4 C5 C6 C7
ID1 Name1 A---B---C---D---E
ID2 Name2 x---B---C---D---E
ID3 Name3 A---B---x---D---x
ID4 Name4 A---x---C---D---E

 

[fneily]

You can use a formula to find if the letter is in the group and then place it,
=IF(ISERROR(FIND("A",C1)>0),"n/a","A")

So you could place that in D1 and then drag copy it down. Then in E1 change the letter to B. etc. Don't forget to change BOTH A's in the formula.