Excel - Check if Value of Two Columns are new in a List and Assing a new ID to them-CodePudding

Good Morning,

I'm trying to formulate something in Excel that allow us to check if the value of two columns are new in a list, and if so, assign a new ID for them. If it's not, let it "Blank" or Assign the same ID that have been assigned before(Either way would work for me).

I'm trying to use something with Count.if, but it doesn't fit. As i'm thinking about this for some time, i decided to look for help.

What i want to do is a formula that solves the "Formula" Columns below:

Space|Name|*Formula*
  1  | AB |  1
  1  | AB |  1
  1  | AB |  1
  1  | CA |  2
  2  | DD |  3
  2  | EE |  4
  2  | EE |  4
  3  | SS |  5
  3  | SS |  5
  1  | ZZ |  6
  1  | AB |  1

CodePudding user response：

Sequential Numbering of Groups of Data

In cell C2 use the following array formula (Ctrl,Shift Enter):

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,MAX(C$1:C1) 1,
INDEX(C$1:C1,MATCH(1,(A$1:A1=A2)*(B$1:B1=B2),0)))

Then copy C2 and pastedown from C3 to the last cell.

If you're satisfied with just numbering each first occurrence then use the following formula:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,MAX(C$1:C1) 1,"")

Both solutions use the headers i.e. the headers must not be numbers.

CodePudding user response：

If you don't mind non-sequential numbering, you can just return the index of the first match found as your identifier:

Copy into C2, then fill down as necessary. The match row stop may need alteration based on how much data you have

=MATCH(A2&"#"&B2, A$2:A$100&"#"&B$2:B$100,0)

Or as an array formula (only need to place in C2);

=MATCH(A2:A11&"#"&B2:B11, A2:A11&"#"&B2:B11,0)