How to assign unique value for each group of duplicates?

[Dulinka_66]

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.

[#2]

@Dulinka_66 

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

You can try this formula.

 

[#3]

@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

[#4 · Accepted solution]

@Dulinka_66 

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

  

[#5]

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

 

[#6]

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!