Comparing 2 sheets to identify records that exist in one, but not the other

I am trying to compare a list of locations from 2023 to a list from 2024. I have an excel file with a sheet for 2023 and one for 2024. The goal is to find the records from 2023 that no longer exist...

excel

m_tarler
Jan 29, 2025
Hans' solution will highlight the lines in the 2023 sheet that are not present in 2024.
My only note would be that although it may not be the case, it is possible for 2 different businesses to have the same street address (e.g. different town or if 2 different businesses are in same 'super structure' like a mall or something). That said you can use:
=ISERROR( XMATCH( $A2 & $B2, '2024 Sheet'!$A:$A & '2024 Sheet'!$B:$B ) )
If you want a list instead of highlighting you can use:
=UNIQUE(VSTACK( 2023!$A1:$A1000 & 2023!$B1:$B1000, 2024!$A1:$A1000 & 2024!$B1:$B1000, 2024!$A1:$A1000 & 2024!$B1:$B1000) , , TRUE)
so basically stack 2023 with 2 cases of 2024 so only cases with 2023 and not 2024 will show only 1x

m_tarler

Bronze Contributor

Jan 29, 2025

Hans' solution will highlight the lines in the 2023 sheet that are not present in 2024.

My only note would be that although it may not be the case, it is possible for 2 different businesses to have the same street address (e.g. different town or if 2 different businesses are in same 'super structure' like a mall or something). That said you can use:

=ISERROR( XMATCH( $A2 & $B2, '2024 Sheet'!$A:$A & '2024 Sheet'!$B:$B ) )

If you want a list instead of highlighting you can use:

=UNIQUE(VSTACK(
          2023!$A1:$A1000 & 2023!$B1:$B1000, 
          2024!$A1:$A1000 & 2024!$B1:$B1000, 
          2024!$A1:$A1000 & 2024!$B1:$B1000)
   , , TRUE)

so basically stack 2023 with 2 cases of 2024 so only cases with 2023 and not 2024 will show only 1x

robertcox87
Copper Contributor
Jan 29, 2025
You are correct, many addresses are used multiple times for different businesses. This formula gave me exactly what I needed. Thanks so much!

Forum Discussion

Comparing 2 sheets to identify records that exist in one, but not the other

Resources