CODEWITHSUNDEEP
google-sheetsmatchgoogle-sheets-formulacountif
Shalin
Shalin

Reputation: 135

Google Sheets: Selecting values in a column that don't exist in another column taking into account number of occurances

I have two column in a Google sheet that have values in them. Column A has all possible values (inclusive duplicate values) and Column B has some of the values in A (also inclusive duplicates).

I wanted to find out which values in Column A do not appear in Column B, also taking into account number of occurrences of these values.

enter image description here

The MATCH function works well however I wanted to have three instances of HQR123 appearing in the Pending column, as there are 4 occurrences of this value in Column A vs only 1 occurrence in Column B. If another instance of HQR123 is entered in Column B then only two instances should appear in the Pending column.

Is this possible?

Thanks and regards, Shalin.

Upvotes: 0

Views: 450

Answers (1)

JPV
JPV

Reputation: 27262

You could try something like

=sort(
 index(
  filter(A2:A, 
   isna(
    match(A2:A&countifs(A2:A, A2:A, row(A2:A), "<="&row(A2:A)), 
    B2:B&countifs(B2:B, B2:B, row(B2:B), "<="&row(B2:B))
  , 0)
 ))))

EXAMPLE

Upvotes: 2

Related Questions