Forum Discussion
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".
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.
- 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!
=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".
