CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
mmartoccia
mmartoccia

Reputation: 5

How to expand upon an existing Google Sheets UNIQUE SORT Formula to exclude items located on another sheets column

I am trying to build a sorted unique list that includes count where the items listed are not part of another worksheet's columned list. The formula below is able to extract a list in its entirety but I want to exclude items that are listed in another sheet's column. I am unsure how to include a VLOOKUP or a FILTER to exclude listed items that are contained on that different sheet.

=sort({UNIQUE(Data!F1:F3001),ARRAYFORMULA(COUNTIF(Data!F1:F3001,UNIQUE(Data!F1:F3001)))},2,false)

The listed data in the other sheet I want to exclude from the list is located in Data2!A1:A3000

Upvotes: 0

Views: 65

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

Personally I would do it with a query - not particularly short, but I think it keeps it tidy:

Data1

=ArrayFormula(query({F1:F3001,countif(Data2!A1:A3001,F1:F3001)},"select Col1,Count(Col1) where Col1 is not null and Col2=0 group by Col1 label Col1 'Value', count(Col1) 'Count'"))

enter image description here

Data2

enter image description here

Upvotes: 1

Related Questions