Forum Discussion

Andy1883's avatar
Andy1883
Copper Contributor
Jun 17, 2024

One of check for difference

Hi Team

 

I am looking for ways to look for differences between Columns B and AA.  B is from my main sheet and AA is from a different Dbase.  In our list of over 250 addresses, we are missing 8 from the main sheet.

This is probably a one of check so it doesn't have to be pretty

Cheers

 

 

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 17, 2024

    Andy1883 To begin with, try Flash-fill. Add column to the blue table and manually enter the first few rows like: 36raddens, 186raddens, 91millbrooke

     

    Excel might automatically recognise a pattern and suggest a solution for all other rows. If it doesn't come up automatically you can go to the Editing group on the Home ribbon and find the Flash-fill button.

    picture taken on a Mac but it's similar on a PC.

    Select the entire column where you started entering the shortened addresses manually. Press Flash-fill. That should do it.

     

    If not, use Text-to-columns on the Data ribbon. Split column B in separate columns based on a space as the delimiter. Now join the number and street columns together (for example =D2&E2 ). If you are using a modern Excel version you can use the TEXTSPLIT and TEXTJOIN functions, of course.

     

    Now sort both the relavant column in the the blue table and the list in AA in ascending order and you should fairly easily see where the two become out of sync. After all, 250 rows of data isn't all that much to go through.

     

     

Resources