Have the need to sort text code-values within same Excel cell

  • Thread starter Thread starter A.X M.D
  • Start date Start date
A

A.X M.D

Got column with thousands of cells containg different codes, e.g., cell P357
--> {X, A, B2, T83, M19}; cell P425 --> {T83, A, X, M19, B2}, etc ... Need
a way to sort these individual cells in a similar order so I can aggregate
the number of cells containing the same set of codes/values ...

I am somehow familiar with the basic Excel functionality but above situation
is really challenging me !

Thanks in advance ..
 
Say each of the cells in column A contains a list of 5 codes separated by
commas.

First copy column A into columns B, C, D, and E.

Next set and AutoFilter for each column:
column A "contains" X
column B "contains" A
column C "contains" B2
column D "contains" T83
column E "contains" M19

The autofilter will display all the rows that have the same contents,
regardless of the order the codes appear.
 
Looks long but that’s' only because I have been pedantically detailed:

1) Let's place save and copy & paste all the entries to an empty worksheet
starting in A1

2) If the { are really part of the cell content -- as in {X, A, B2, T83,
M19}; : use Edit | Find & Replace to replace these by nothing. Now you
have, for example, X, A, B2, T83, M19

3) Select all of the data in column A, use Data | Text to Column with
Delimited by comma
Now you have the the data broken into separate cells
X A B2 T83 M19

4) Select all the data and use Data Sort; specify sort left to right: giving
A B2 M19 T83 X

5) In F1 enter
EITHER: =A1&", "&B1&", "&C1&", "&D1&", "&E1 or
OR: ="{"&A1&", "&B1&", "&C1&", "&D1&", "&E1&"}"
Copy down the column by double clicking F1's fill handle

6) When you are satisfied all is correct you could Copy and Paste Special
with Values specified this range over top of the data in P of original
worksheet, or into F of the current worksheet for analysis.
best wishes
 
Back
Top