-1

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

Nikos Hidalgo
  • 3,666
  • 9
  • 25
  • 39
Tommy
  • 3
  • 3
  • First create a helper column, say D. In D2 enter `=A2&B2` and copy it down and then you can use countifs to check for its duplicate. Enter this formula in C2 and copy it down `=if(countifs($D$1:$D2,D2)=1,Row()&"_"&A2&"_"B2,"")` – Naresh Mar 20 '20 at 07:12
  • Oh! Better if your helper column D2 `=Min(A2:B2)&Max(A2:B2)` if those contain numbers only.. for text you can use small and large, I think... And then C2 can be `=if(countifs($D$1:$D2,D2)=1,Row()&Min(A2:B2)&Max(A2:B2),"")` and copy those formulas down – Naresh Mar 20 '20 at 07:36
  • Hi @NareshBhople. Thanks for your help! I receive an error message "There's a problem with this formula." =if(countifs($D$1:$D2,D2)=1,Row()&""&A2&""B2,"").When I use the following formula if(countifs($D$1:$D2,D2)=1,Row()&Min(A2:B2)&Max(A2:B2),""). The Unique ID column is concatenated using columns A & B, and a sequential number gets added to the prefix. – Tommy Mar 20 '20 at 07:58
  • Yes, Thats the purpose of creating unique id for each record, isnt it? Or something else you are looking for? If you are just looking for unique combinations of a and b then remove `ROW()&` from the formula – Naresh Mar 20 '20 at 08:06
  • @nareshbhople, two of the Unique IDs were blank. My goal is to have a Unique ID when the Record Numbers are not duplicated, but repeat the Unique ID when a Record is duplicated (based on the example/snapshot I provided). – Tommy Mar 20 '20 at 08:12
  • Got it now. Then you dont even need a helper column because as mentioned earlier D column will give you that result. D2 `=Min(A2:B2)&"_"&Max(A2:B2)` is your unique combination number and copy it down. It will give combinations with min 1st and max 2nd number. – Naresh Mar 20 '20 at 08:17
  • @NareshBhople, sorry I am not doing a good job explaining. If record 110035 is in column A and 2000449 is in column B for row 3. If record 110035 is in column A and 2017130 is in column B for row 4. If record 2026304 is in column A and 2017130 is in column B for row 10, I should have the same Reference ID listed for each of these rows. I need to be able to group these line items together in order to perform a duplicate review. I want to sort based on a unique identifier so I can review duplicates that appear in two columns. – Tommy Mar 20 '20 at 08:26
  • Are you dealing with BOM(Bill of Materials) or some king of Parent and Child Coding? You mentioned "I have tried using the formulas you previously posted to resolve my issue".. I would say, please edit your question to clarify all your point in the comments and also copy and paste the previous link in the question. – Naresh Mar 20 '20 at 08:30
  • @NareshBhople I am dealing with vendor records. Duplicates exists, so I performed a fuzzy lookup and now I have different variation of vendor numbers in two columns. I need to tie the duplicate records together, so that I will have total visibility to potential duplicates. Here is the link to a question that someone else posted (which does not completely help me) https://stackoverflow.com/questions/42072127/create-new-unique-id-based-on-two-columns-excel. If you review my attachment, it should show you the different variations that exist. – Tommy Mar 20 '20 at 08:37

1 Answers1

0

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

Naresh
  • 2,984
  • 2
  • 9
  • 15