Search code examples
exceldatabaseformulaunique-keyunique-id

Creating keys/unique values for rows

I couldn't find the answer here, probably because of a lack of understanding the terminology.

I have created a sheet with several classes, and want to assign a unique ID (starting at "1") to each cell in the "Genus_ID" column. It must restart at 1 every time it identifies that it belongs to a new Family or Subfamily. Basically how a relational database would be designed.

Here is a sample of my sheet:

enter image description here

The Genus_ID is counting upwards, but it should reset to "1" at every new Family OR Subfamily.

Also, in I3 it shouldn't say "Porifera", but it should iterate the previous value ("1").

So, like this:

enter image description here

I'm using the following formula right now, it's close, but nowhere near perfect yet:

=IF(COUNTIFS(A$2:H2,H2)=1,MAX(I$1:I1)+1,VLOOKUP(A2,A$1:I1,2,0))

Can anyone help me out?

Thanks a lot!

Solution

  • If FAMILY is column D and GENUS is column H, try this in I2 and copy down

    =IF(D2<>D1,1,IF(H2=H1,I1,I1+1))

    If SUBFAMILY is in column F try this

    =IF(OR(D2<>D1,F2<>F1),1,IF(H2=H1,I1,I1+1))