Sorting a Long List of Single Columned Data in Excel

0

I have a long list of single columned data which needs organising.

The data consists of account codes and names. The raw data looks like whats on the image below on the left, what I'd like it transformed into is on the right.

Sample Data

If this was just a small sample I'd do it by hand, with it being about 4000 rows is there any easier way to do this?

Some things to note:
As in the sample above, the list of names for each account code always appear three rows after the code. Then the next account code comes one row after the last name.

BlacklabM

Posted 2017-03-01T10:43:01.707

Reputation: 1

This is likely to need a macro, especially given the variable size of each data set. It may be possible to do it with a very, very complex formula, but it won't be straightforward. Do you have a master list of the account codes or similar anywhere? Do any account codes repeat? – Werrf – 2017-03-01T13:57:26.827

Yes, I have a list of all of the account codes - approx. 500 of them. All of the codes are unique, it's only the account names that repeat. – BlacklabM – 2017-03-02T09:59:36.593

Answers

0

First Copy Column A to Column I and remove duplicates from it and all AAA, AAB...
You have to define each range in Column A under AAA (The names and blank without AAA, AAB) the names between AAA and AAB Define name FirstAAA, SecondAAB...
You can give the name you want and replace it in Match formula

In J3 as shown in your Table write the following:

=IF(ISNA(MATCH(I3,FirstAAA,0)),"","X")
and drag it down
In K3 write:
=IF(ISNA(MATCH(I3,SecondAAB,0)),"","X")
and drag it down
In L3 write:
=IF(ISNA(MATCH(I3,ThirdAAC,0)),"","X")
and drag it down
In M3 write:
=IF(ISNA(MATCH(I3,FourthAAD,0)),"","X")
and drag it down
In N3 write:
=IF(ISNA(MATCH(I3,FifthAAE,0)),"","X")
and drag it down

yass

Posted 2017-03-01T10:43:01.707

Reputation: 2 409

Asked: 2017-03-01T10:43:01.707

Viewed: 51 times

Active: 2017-03-01T14:41:50.273