Forum Discussion
Solved
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.
=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 Replies
OliverScheurich 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 =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.
