Counting recurring items in list

[Kriszti]

Hi there,

I have a long list of data (over 2500 rows) and I want to create a unique identifier for each (to be able to compare later with another set of data).


I started with sorting them first by Ledger-Code and then Monetary_Amount and I want to number each item in the list so it counts up when the Monetary_Amount item is the same, but starts again from 1 when data changes.

See this example:

Ledger_Code Monetary_Amount Item count

4040 100 1

4040 100 2

4040 100 3

4040 100 4

4040 100 5

4040 200 1

4040 200 2

4040 500 1

4040 1000 1

4210 500 1

4210 500 2

4210 500 3

4210 500 4

4210 1000 1

4210 1000 2


My aim is that when this count is done I will be able to create a further column by pulling together all three values in one cell, therefore creating unique values. - using =A1&" "&A2&" "&A3 formula - this would give me the unique comparable list.

For example:

4040 100 1

4040 100 2

4040 100 3

4040 100 4

4040 100 5

4040 200 1


My question is, is there any tool/trick I can use to create "Item Count" as I explained above? I just don't feel like going through all 2500 records manually... I'm sure there must be something clever, but I'm not quite at that level just yet.


All advice and help will be much appreciated - I'm working for a children's charity and this is one of their projects.


Thank you,

KF

 

[Kriszti]

Ok, sorry - I think I've found the answer in an older post!!!


Thanks so much for running such a wonderful site!

 

[Hui]

Kriszti

Firstly, welcome to the Chandoo.org Forums


I'd do as you've done add the helper column

Copy and Paste the data as Values

Then use an Advanced Filter on the New Column and Copy it to a new Range and only select Unique Items