Identifying Occurences of a value, and numbering according to date

[Feteha12]

Hi All,

I have a tricky one for you. Its regarding counting number of occurences of a value, and numbering it earliest oldest starting with 1.

I have a set of values, lets call them customers in Column A, next to each customer is a date in Column B. Each date represents the day he bought products in.

I am trying to number the invoices for that each customer based on the date, first invoice getting a 1, second getting as per the date gets a 2 and so on.... in Column C. This for example enables me to filter by "third" invoice for all customers, regardless when he bought as long as it was third purchase in that month.

Essentially i want a formula that looks for all occurrences of a value (customer), and gives that purchase instance a number according to dates earliest to newest. Check the example attached. This is very tough manually, considering in a given month i can have up to 500/600 unique customers, and each can buy up to 5 or 6 times.

Capture2.JPG

Hope its clear enough, i thought of going for a pivot, but it got very messy

Thanks.

[Tony Valko]

Maybe this...

=COUNTIFS(A$3:A3,A3,B$3:B3,B3)

Copy down as needed.

[Feteha12]

Thanks but it doesnt seem to tackle it, am i missing something?

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tony Valko]

After looking at your sequence more closely I have to say that I don't understand how you arrive at those results.

For example, row 12...

Customer3...18-Jan...4

How do you arrive at 4?

Customer3 doesn't even appear 4 times and only appears once on 18-Jan.

[Feteha12]

Yes you are right, its a mistake when filled manually, it should read 3 not 4..
Will upload that workbook mockup now

[Feteha12]

Fixed the snapshot above, and attached is the sample workbook.

[JohnTopley]

Try

=COUNTIF(A$3:A3,A3)

[Feteha12]

THANKS MATE...that actually works, the only downside is it assumed the data was entered chronologically, i.e: if the data entry person enters the older invoices then newer and so on, otherwise the count will get screwed...but i think i can work with that....

THanks again !

[Tony Valko]

Data Range

	A	B	C
2	CUSTOMERS	DATE	Invoice sequence
3	Customer 1	1-Jan	1
4	Customer 1	4-Jan	2
5	Customer 1	10-Jan	3
6	Customer 2	3-Jan	1
7	Customer 2	25-Jan	2
8	Customer 2	29-Jan	3
9	Customer 3	10-Jan	1
10	Customer 3	15-Jan	2
11	Customer 3	18-Jan	3
12	Customer 5	2-Jan	1
13	Customer 5	3-Jan	2
14	Customer 1	19-Jan	4
15	Customer 2	30-Jan	4
16	Customer 1	25th jan	5
17	customer 3	28-Jan	4
18	Customer 5	9-Jan	3
19	Customer 6	17-Jan	1

This formula entered in C3 and copied down:

=COUNTIF(A$3:A3,A3)

[Tony Valko]

the only downside is it assumed the data was entered chronologically, i.e: if the data entry person enters the older invoices then newer and so on, otherwise the count will get screwed...but i think i can work with that....

If you want them number based on the dates try this version:

=SUMPRODUCT(--(A$3:A$19=A3),--(B3>B$3:B$19))+1

[Feteha12]

that you mate...it was easy enough that i missed it...its always good to get a second eye

will just make sure data is always organised chronologically and it should be good.

[Feteha12]

that you mate...it was easy enough that i missed it...its always good to get a second eye

will just make sure data is always organised chronologically and it should be good.