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...
=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".
=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.
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!
Thank you so much for such a quick and adequate solution!