Forum Discussion
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 in 2024. I've tried using highlight cell rules to compare addresses, but I'm doing something wrong. I feel like there is a simple way to do this using VLOOKUP, but I have no idea how to use it. Attached is a screenshot of how the spreadsheets are structured. Thanks for your assistance!
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
4 Replies
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
You are correct, many addresses are used multiple times for different businesses. This formula gave me exactly what I needed. Thanks so much!
Select B2:B1000 (or however far down the data extend) on the 2023 sheet. B2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=ISERROR(XMATCH(B2, '2024 Sheet'!B:B))
where 2024 Sheet is the name of the sheet for 2024.
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.Thank you!
