# Return Sequential Numbers Based on Unique Values

1. ## 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

2. ## Re: Return Sequential Numbers Based on Unique Values

=sumproduct(--(\$A\$2:A2=A2),--(\$D\$2:D2=D2))

3. ## Re: Return Sequential Numbers Based on Unique Values

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.

4. ## Re: Return Sequential Numbers Based on Unique Values

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)

5. ## Re: Return Sequential Numbers Based on Unique Values

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

6. ## Re: Return Sequential Numbers Based on Unique Values

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?

7. ## Re: Return Sequential Numbers Based on Unique Values

@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...

8. ## Re: Return Sequential Numbers Based on Unique Values

with a pivot table.

see the attached file.

9. ## Re: Return Sequential Numbers Based on Unique Values

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.

10. ## Re: Return Sequential Numbers Based on Unique Values

@ 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...

11. ## Re: Return Sequential Numbers Based on Unique Values

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

12. ## Re: Return Sequential Numbers Based on Unique Values

No Problem, thanks for your help...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1