SOLVED

How to assign unique value for each group of duplicates?

New Contributor

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?

 

CountryNumberUnified Number
Belgium54 
Belgium  
Belgium54 
Belgium  
Belgium  
Belgium54 
Great Britan57 
Great Britan57 
Great Britan57 
Great Britan  
Great Britan  
Japan565 
Japan  
Japan565 
Japan  
Japan565 
Japan565 
Kashmere87 
Netherlands47 
Nigeria345 
Nigeria  
Nigeria345 
Nigeria345 
Nigeria  
Nigeria345 
Poland45 
Poland  
Poland  
Poland45 
Poland45 
Poland  
Poland45 
Senegal654 
Sweden78 
Sweden78 
Sweden  
Sweden78 

I would be extremally appreciated for help.

5 Replies

@Dulinka_66 

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

You can try this formula.

value for group of duplicates.JPG 

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

 

ountryNumberUnified Number
Belgium 0
Belgium 0
Belgium540
Belgium 0
Belgium 0
Belgium540
Great Britan5757
Great Britan5757
best response confirmed by Dulinka_66 (New Contributor)
Solution

@Dulinka_66 

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

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

unified number.JPGThen 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.

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

all unified numbers.JPG  

@Dulinka_66 

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

country unified number.JPG 

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!