excelexcel-indirectexcel-match

Create New Unique ID Based on Linked Values in Two Columns - Excel

I have tried using the formulas you previously posted to resolve my issue, but I am showing a 3 in column C for some of the Unique IDs instead of a 2. My goal is to assign a Unique ID whenever a record in column A is linked to a record in column B (1:1 or 1:may relationships).

enter image description here

Solution

  • C1 = 0

    Then formula in C2

    =IF(AND(COUNTIFS($A$1:$B2,A2)=1,COUNTIFS($A$1:$B2,B2)=1),C1+1,IFERROR(IFERROR(VLOOKUP(A2,$A$1:C1,3,FALSE),VLOOKUP(B2,$B$1:C1,2,FALSE)),IFERROR(VLOOKUP(A2,$B$1:C1,2,FALSE),VLOOKUP(B2,$A$1:C1,3,FALSE))))

    and then copy down the formula

    or you can start with C2 = 1 Then formula in C3

    =IF(AND(COUNTIFS($A$1:$B3,A3)=1,COUNTIFS($A$1:$B3,B3)=1),C2+1,IFERROR(IFERROR(VLOOKUP(A3,$A$1:C2,3,FALSE),VLOOKUP(B3,$B$1:C2,2,FALSE)),IFERROR(VLOOKUP(A3,$B$1:C2,2,FALSE),VLOOKUP(B3,$A$1:C2,3,FALSE))))

    Then copy down the formula

    enter image description here