Forum Discussion

Dulinka_66's avatar
Dulinka_66
Copper Contributor
Nov 28, 2022
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...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Nov 28, 2022

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

      

Dulinka_66's avatar
Dulinka_66
Copper Contributor
Nov 28, 2022

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?

 

ountryNumberUnified Number
Belgium 0
Belgium 0
Belgium540
Belgium 0
Belgium 0
Belgium540
Great Britan5757
Great Britan5757
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 28, 2022

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

  

Resources