Return Sequential Numbers Based on Unique Values

I am looking for a formula that will return sequential numbers based on the first itteration of a combination of two ranges and then zero for the other itterations of the same combination. I've attached a sample to demonstrate.

Many thanks

Hi tim,

I'm afraid this doesn't give the expected result, it gives the next sequential number to each instance of the same column A and D combination, what I need is the same number for each combination (with the first instance showing the sequential number and other instances showing zero.

If you add this in column J and then compare it to the expected result in column I you'll see this doesn't give the correct result.

I could possibly get away witout having the zero's and just having the same number for each occurance of the same combination for columns A and D.

I think you have some errors in col I.

Row\Col	A	D	I	J	K
1	Code	Supplier	Desired	Helper	Formula
2	AB123	12378	1	AB123-12378	1
3	AB124	26546	1	AB124-26546	1
4	AB123	54687	2	AB123-54687	2
5	AB124	46390	1	AB124-46390	2
6	AB127	62983	1	AB127-62983	1
7	AB123	54378	3	AB123-54378	3
8	AB123	12378	0	AB123-12378	0
9	AB123	12378	0	AB123-12378	0
10	AB131	87345	1	AB131-87345	1
11	AB132	87345	1	AB132-87345	1
12	AB124	26546	0	AB124-26546	0
13	AB124	26546	0	AB124-26546	0
14	AB133	87345	1	AB133-87345	1
15	AB133	87345	0	AB133-87345	0
16	AB124	26546	0	AB124-26546	0
17	AB123	46390	4	AB123-46390	4
18	AB136	46390	1	AB136-46390	1
19	AB124	46390	0	AB124-46390	0
20	AB141	87345	1	AB141-87345	1
21	AB127	87345	1	AB127-87345	2
22	AB124	87345	1	AB124-87345	3
23	AB144	87345	1	AB144-87345	1
24	AB130	54687	1	AB130-54687	1
25	AB146	54378	1	AB146-54378	1
26	AB147	87345	1	AB147-87345	1
27	AB123	54687	0	AB123-54687	0
28	AB149	54687	1	AB149-54687	1
29	AB127	62983	0	AB127-62983	0
30	AB124	54378	1	AB124-54378	4

J2 and down: =A2 & "-" & D2

In K2, confirmed with Ctrl+Shift+Enter and copied down,

=IF(COUNTIF($J$1:K1, J2), 0, MAX(IF($A$1:$A1=A2, $K$1:K1)) + 1)

i2=IF(ROWS(K$2:K2)=1,1,IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2)>1,0,MAX(INDEX((A1:A$2=A2)*I1:I$2,0))+1))

Please Login or Register  to view this content.

try this and copy towards down

I came up with:
H2:

Please Login or Register  to view this content.

I2:

Please Login or Register  to view this content.

with results similar to shg's and discrepancy in I5, 21,22 and 30.
What is correct?

@shg

Yes, you are right, there was an error, thanks for picking that up, I've corrected it in the attachment.

Thanks also to Bebo and nflsales... all formula's work, many thanks... I'm curious to understand if any one formula is more efficient than any other as I need to apply this to some 250,000 rows!

The second challenge is to make the 1st instance, i.e. the instance that returns 1 for each combination of A and D be the one where the SUM of the values for that combination in column G is the highest...

So taking AB123 as an example, the SUM of each combination is

AB123 - 12378 = £25,352.00
AB123 - 46390 = £2,639.00
AB123 - 54378 = £15,527.00
AB123 - 54687 = £28,438.00

The key is that using the same logic AB123 - 54687 now should = 1. In an ideal world and probably formulaically it would be great if it worked in order of value so that

AB123 - 12378 = 2
AB123 - 46390 = 4
AB123 - 54378 = 3
AB123 - 54687 = 1

Is that possible or asking the impossible?

Many thanks to everyone, it's really interesting to see the differing approcaches...

with a pivot table.

see the attached file.

Based on the file from your another thread, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In K2

Please Login or Register  to view this content.

and copy down.

@ oeldere

Sadly, whilst a pivot table would give me the answer, I can't practically use one in this instance owing to the nature of the structure of the spreadsheet, but many thanks for your reply.


@sktneer

That gives the perfect answer in a formula which is exactly what I need, I now just need to break it down to understand it, many thanks...

You're welcome. Glad I could help.
Thanks for the feedback also.

No Problem, thanks for your help...