SOLVED

New Contributor

# How to assign unique value for each group of duplicates?

Hello,

I have huge database stored in Excel for some reason. See the small example below.

Problem statement: every country has a unique number assigned in this table. But not every duplicate value is sharing this number. How can I match this unique number to every group of duplicates? For example, for every "Belgium" value in the Country find non empty cell and match with all "Belgium" values (in the Unified Number column). How to do it without VBA?

 Country Number Unified Number Belgium 54 Belgium Belgium 54 Belgium Belgium Belgium 54 Great Britan 57 Great Britan 57 Great Britan 57 Great Britan Great Britan Japan 565 Japan Japan 565 Japan Japan 565 Japan 565 Kashmere 87 Netherlands 47 Nigeria 345 Nigeria Nigeria 345 Nigeria 345 Nigeria Nigeria 345 Poland 45 Poland Poland Poland 45 Poland 45 Poland Poland 45 Senegal 654 Sweden 78 Sweden 78 Sweden Sweden 78

I would be extremally appreciated for help.

5 Replies

# Re: How to assign unique value for each group of duplicates?

``=INDEX(\$B\$2:B2,MATCH(A2,\$A\$2:A2,0))``

You can try this formula.

# Re: How to assign unique value for each group of duplicates?

@Quadruple_Pawn Thank you, it's working in this example. INDEX and MATCH is finding first value and assigning it to the rest of duplicates. But if the first value is empty it will assign null. See below, I've removed first value from Belgium. How to make it match first non empty value?

 ountry Number Unified Number Belgium 0 Belgium 0 Belgium 54 0 Belgium 0 Belgium 0 Belgium 54 0 Great Britan 57 57 Great Britan 57 57
best response confirmed by Dulinka_66 (New Contributor)
Solution

# Re: How to assign unique value for each group of duplicates?

``=INDEX(\$B\$2:B2,MATCH(A2,\$A\$2:A2,0))``

You can enter this formula in cell C2 and copy down.

Then you can select range A1:B9 and apply a custom sort. In the example i checked "My table has headers" (in german: Daten haben Überschriften) and selected "largest to smallest"  (in german: Nach Größe (absteigend) ). Then i clicked ok.

The result is that INDEX and MATCH adds all numbers in column C "unified number".

# Re: How to assign unique value for each group of duplicates?

``=INDEX(\$B\$2:\$B\$9,SMALL(IF((\$A\$2:\$A\$9=A2)*(\$B\$2:\$B\$9<>""),ROW(\$B\$2:\$B\$9)-1),1))``

An alternative could be this formula if you don't want to sort the data. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

# Re: How to assign unique value for each group of duplicates?

Unfortunately, I'm working on Office365 but it's ok, the first solution is working for me. I set a calculation column and after sorting I'm copying values without formula to the designated column.

Thank you so much for such a quick and adequate solution!