+ Reply to Thread
Results 1 to 16 of 16

Find Missing Entries when comparing two columns

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Find Missing Entries when comparing two columns

    I have a set of source codes in Column A and a set of what should be matching codes in Column B, however there are some codes missing in Column B. I'm trying to find a way to quickly identify the missing codes in Column B, either by either having a macro that somehow moves the entries in Column B so they match those in Column A, giving the result shown in Columns E and F (in the attached, I've only shown the first 30 rows).

    Alternatively, a method that highlights which entries in Column A are missing in Column B could work, using Conditional Formatting or another, posibly better idea is to extract the entries in Column A that don't appear in Column B and copies these entries into another column (without any gaps).

    I'm not really sure where to start to achieve this?

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find Missing Entries when comparing two columns

    I could use =ISNA(MATCH(A2,$B:$B,0)) but ideally I would prefer something that automatically moves the entries in Column B to their respective matching row in Column A, so I can visually see where the missing entries are if that makes sense, as per Columns E and F.

    Many thanks

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,778

    Re: Find Missing Entries when comparing two columns

    Perhaps a macro like this?

    Please Login or Register  to view this content.
    Will put data from the A column in E and if matching values found in the B column then this value is added in the F column.

    Alf
    Last edited by Alf; 07-30-2013 at 12:44 PM.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find Missing Entries when comparing two columns

    HangMan,

    Attached is a modified version of the workbook you posted.
    I used conditional formatting to find non-matching cells from the 'Compare' sheet in column A and then filters by color. I then copied those items and pasted them as values into sheet 'Results'.

    The conditional format formula applied to column A is:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find Missing Entries when comparing two columns

    Alf,

    That is absolutely brilliant and does exactly what I need...

    Many thanks for taking the time to reply

  6. #6
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find Missing Entries when comparing two columns

    Tigeravatar,

    That works well, many thanks too for taking the time to reply, it has helped me to focus my thinking.

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find Missing Entries when comparing two columns

    As far as the macro solution goes, here is Alf's macro reworked a bit in order to greatly improve its speed (It finished on the sample workbook you provided in about 1 second):
    Please Login or Register  to view this content.

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,778

    Re: Find Missing Entries when comparing two columns

    Glad to be of help and thanks for feedback.

    Since your problem seems solved now don't forget to mark your thread solved as well.

    Alf

  9. #9
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find Missing Entries when comparing two columns

    tigeravatar,

    Many thanks, that makes a massive difference. Alf's original code took several minutes to run and like you say, you're updated code runs in less than a second. Perfect...

    Many thanks both

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,778

    Re: Find Missing Entries when comparing two columns

    Strange running both macros I got following result my macro 3, 4, 3 seconds
    tigeravatrs modification 4, 4, 4 seconds

    I got a QuadCore AMD Phenom 965 3400 Mhz CPU, OS and Office on an OCZ SSD disk and PC has 8 GB DDR3 Ram so system should be fast but several minutes??

    Alf

  11. #11
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find Missing Entries when comparing two columns

    Hi Alf,

    Strange, I ran your code initially and it literally took several minutes to run, then I ran tigeravatar's code and it took around a second, not sure why? Not sure how to analyze...

    ...but thanks to you both for a very neat solution.

  12. #12
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find Missing Entries when comparing two columns

    Attached is a modified version of the original workbook. It contains both Alf's original code and my modified version of it. You can click the buttons to have it run the appropriate code and it will show a msgbox with how long it took. On my machine, Alf's macro averaged 1.109375 seconds and the modified version averaged 0.859375 seconds.

    I would like to state that I am not trying to disparage or put down Alf's work and I hope I'm not coming off as condescending I'm just trying to demonstrate the advantage of using an array vs writing to each cell individually.
    Attached Files Attached Files

  13. #13
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find Missing Entries when comparing two columns

    As for the speed difference that HangMan is seeing, its probable that his actual workbook has a lot more data, or it has formulas. A lot of the speed difference could probably be made up for in Alf's code by switching calculation to Manual, and then back to Automatic at the end.

  14. #14
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,778

    Re: Find Missing Entries when comparing two columns

    I would like to state that I am not trying to disparage or put down Alf's work and I hope I'm not coming off as condescending
    No I don't feel that way. What I'm rather concerned about is that if my PC should be much faster than the average OP then I would never consider the efficiency of the solutions I propose and not spend sufficient time improving my suggestions based on "speed" efficiency.

    Must admit I never considered your comments " its probable that his actual workbook has a lot more data, or it has formulas" a valid point I should have thought of my self.

    Just tested your uploaded file my result 3,695 s and your modification took 3,492 so yes it's definitely better!

    Since your result was 1,109 and 0,859 I'm a bit curious what kind of PC you have? CPU, RAM and HD would be interesting to know.

    Alf

  15. #15
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find Missing Entries when comparing two columns

    I am running an Intel i7 920 at 2.67 GHz processor with 6 GB of DDR3 ram (PC10666) and hard drive is a 2 TB 7200 RPM internal hard drive. They were really good specs 3 years ago, but I'm probably due for an upgrade now. Not sure why yours is running in the 3 to 4 second range to complete either macro though

  16. #16
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,778

    Re: Find Missing Entries when comparing two columns

    Not sure why yours is running in the 3 to 4 second range to complete
    Perhaps the AMD versus the Intel effect?

    And I thought my PC was fast. Sight!

    Alf

+ 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. [SOLVED] Comparing two columns of words to find out missing
    By Karnik in forum Excel General
    Replies: 4
    Last Post: 10-29-2012, 10:12 PM
  2. Replies: 4
    Last Post: 07-29-2012, 08:40 AM
  3. Replies: 1
    Last Post: 05-30-2012, 06:29 AM
  4. Comparing Columns & return missing & new data using VBA
    By DMBeer41 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-20-2011, 11:50 AM
  5. [SOLVED] 2 workbooks - how do I find duplicate entries by comparing the two
    By queen on in forum Excel General
    Replies: 2
    Last Post: 09-20-2005, 07:05 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