+ Reply to Thread
Results 1 to 7 of 7

How to Return Differences between Two Columns?

  1. #1
    Registered User
    Join Date
    07-22-2021
    Location
    US
    MS-Off Ver
    Mac Version 16.58
    Posts
    21

    How to Return Differences between Two Columns?

    Hello,

    What I'm trying to do is to return differences between two IDs. In the file, column A contains new IDs, and column B contains names associated with it. There's also a column with Old IDs.
    I need:
    1. to pull IDs from column A that aren't in an old column (pull the differences)
    2. to pull list of names from column B that will correspond to the IDs from column A

    I hope someone can help here. I tried different formulas, and conditional formatting, but for some reason it doesn't pull all the different IDs.

    Thanks.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: How to Return Differences between Two Columns?

    Select CF, set New Rule as
    =ISERROR(MATCH(A2,$D$2:$D$10,0)) and set "Fill" as whatever colour you want.
    Apply to A$2:A$10

    Values in Col A not in Col D will be shaded.

    Ochimus
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-22-2021
    Location
    US
    MS-Off Ver
    Mac Version 16.58
    Posts
    21

    Re: How to Return Differences between Two Columns?

    Thanks Ochimus, I guess if I use CF, then I'm having trouble deleting the CF cells/rows. I have a very large file - 50k rows and not sure how to delete these CF rows.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to Return Differences between Two Columns?

    I inserted an extra column between Name and Old ID

    For your profiled version of Excel in cell C2 you will need to array enter this formula, fill down and across column D until you get blanks.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Looks like this at my end.


    A
    B
    C
    D
    E
    1
    ID
    Name
    Old ID
    2
    1234567
    Apples
    1234567
    Apples
    9876543
    3
    2456778
    Oranges
    2456778
    Oranges
    43219056
    4
    4567890
    Bananas
    4567890
    Bananas
    6594032
    5
    345321
    Pears
    43219056
    Avocados
    1987234
    6
    9876543
    Nectarines
    11122233
    7
    9873456
    Plums
    99884455
    8
    2345678
    Cherries
    22335577
    9
    4567934
    Pineapples
    7644311
    10
    43219056
    Avocados
    10293485
    11
    5646870
    Grapes
    12
    43192355
    Strawberries
    13
    4563845
    Blueberries
    14
    6594032
    Mangos
    15
    3456092
    Blackberries
    16
    10293485
    Raspberries
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to Return Differences between Two Columns?

    Please ignore my previous post. I interpreted wrong.

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: How to Return Differences between Two Columns?

    Try Cell G2 array formula , Drag down

    HTML Code: 

  7. #7
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: How to Return Differences between Two Columns?

    G2=IFERROR(INDEX(A:A,SMALL(IF(ISERROR(MATCH($A$2:$A$16,$D:$D,)),ROW($A$2:$A$16),"/"),ROW(A1))),"") control+shift+enter, then copy to right and down

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare strings and return differences in Excel
    By dfghjk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2020, 02:43 PM
  2. Replies: 9
    Last Post: 01-13-2020, 10:16 AM
  3. VBA script to compare two workbooks and return differences between the two?
    By pizzaguy4487 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2018, 12:31 AM
  4. Compare two columns and return differences
    By twachtmann56 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2016, 08:10 PM
  5. [SOLVED] Compare data in Columns A & B - Show differences in Columns C & D
    By syncguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2015, 09:59 PM
  6. Replies: 0
    Last Post: 07-17-2014, 10:06 AM
  7. Compare Worksheets and Return Differences
    By bkeller83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2009, 01:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1