Filter mulitple rows by matching data in one column

New Contributor

I have an grid of data where we have many companies listed many times for different transactions all of which have a location of the transaction. I woud like to find a list of all companies who have never had a transaction in England but have done so elsewhere.

ABC LtdScotland
ABC LtdEngland
ABC LtdWales
ABC LtdIreland
DEF LtdScotland
DEF LtdWales

In this sheet I would be looking to return a result of DEF Ltd as across all rows they do not have any records in England. Please can someone asssist in the correct formula or use of pivot table? Thank you in advance

1 Reply

@JulianpIDG Since you mention "many companies listed", you may consider PowerQuery (if you're not a Mac). Alternatively, if you are an MS365 subscriber, you can do it in a few steps with UNIQUE, FILTER. Working examples in the attached workbook.