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

user_13602955 0
Viewed 44

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
count excel-formula excel
2 Answers

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.

user_98140690

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)
user_36888610
Related