Forum Discussion

restats's avatar
restats
Copper Contributor
Jul 30, 2023
Solved

Extracting Records

How can I extract the records that are not included in two databases? In combining the two databases Excel allows me to delete duplicates but can I use this to eliminate all that are two of a kind l...
excel
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Jul 30, 2023

    restats 

    There are some addresses that should be the same but differ slightly:

    10101 NE 46th Ave  Vancouver, WA 98686 
    10101 NE 46th Ave, Vancouver, WA 98686

    One option is to add helper columns:

    AD2 contains the formula =TRIM(SUBSTITUTE(G2, ",", ""))

    This eliminates differences such as comma vs no comma and double space vs single space.

    It does not eliminate spelling differences.

    AE2 contains the formula =COUNTIF(AD:AD, AD2)=1

    This returns TRUE if the helper address is unique.

    You can then filter on TRUE and copy the selected rows to another sheet, either manually using AutoFilter or using Advanced Filter or using the FILTER function:

     

    =FILTER(CJun23!A2:AD1323,CJun23!AE2:AE1323)

     

restats's avatar
restats
Copper Contributor
Jul 30, 2023
Let me correct this query because my data base is made up of properties. What I want to eliminate are the records that have the same addresses even though the other fields may have some different values. I attempted to use the Unique function but learned that the record fields must all be the same for elimination.
HansVogelaar's avatar
HansVogelaar
MVP
Jul 30, 2023

restats 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

  • restats's avatar
    restats
    Copper Contributor
    Jul 30, 2023
    https://1drv.ms/x/s!Arvr7szuU5p4hNM4HW3Gh0B_M_C1Fw?e=QRfiaX
    Here is the database
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 30, 2023

      restats 

      There are some addresses that should be the same but differ slightly:

      10101 NE 46th Ave  Vancouver, WA 98686 
      10101 NE 46th Ave, Vancouver, WA 98686

      One option is to add helper columns:

      AD2 contains the formula =TRIM(SUBSTITUTE(G2, ",", ""))

      This eliminates differences such as comma vs no comma and double space vs single space.

      It does not eliminate spelling differences.

      AE2 contains the formula =COUNTIF(AD:AD, AD2)=1

      This returns TRUE if the helper address is unique.

      You can then filter on TRUE and copy the selected rows to another sheet, either manually using AutoFilter or using Advanced Filter or using the FILTER function:

       

      =FILTER(CJun23!A2:AD1323,CJun23!AE2:AE1323)

       

Resources