CODEWITHSUNDEEP
excelvba
C_psy
C_psy

Reputation: 647

In VBA, check matches by grouping variable

I need to produce a new column that checks for matches in the m1, m2 columns, per ID and returns a character, depending on matching status. So, for each ID, if m1, m2 match values, return 'a', if m1 = 'No', return 'b' and if m2 = 'No', return 'c'. Example below

ID  m1  m2  new_col
111 1   1   a
111 2   2   a
222 1   1   a
222 No  2   b
222 2   3   a
333 1   No  c
333 2   1   a
333 3   2   a
333 4   3   a

Upvotes: 0

Views: 49

Answers (1)

Mike67
Mike67

Reputation: 11342

You can use a formula to calculate the new column:

Assuming your data starts at A2, here is the formula for E2:

=IF(IF(B2="No","b",IF(C2="No","c",COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,C2)))=1,"a",IF(B2="No","b",IF(C2="No","c",COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,C2))))

Note that the formula only checks the first 9 rows based on your sample data.

Also note that the 5th line (222\2\3) has an unknown result because none of the criteria match.

Here is the output with the formula:

Excel Table

Upvotes: 1

Related Questions