+ Reply to Thread
Results 1 to 13 of 13

Compare two tables - keep only duplicates ...

  1. #1
    Registered User
    Join Date
    06-08-2014
    Posts
    7

    Compare two tables - keep only duplicates ...

    Dear fellow Excel professionals,

    I would really appreciate your help on this example. Since I'm total Excel newbie, please don't be harsh on me... at least this time
    Please lead me in the right direction where should I start digging. Probably there is some pretty simple"something" for this.

    I'm having one Initial table where column A should be compared to another table with only one column. At the end I want to be left only with rows from Initial table which values can be found on a second table.
    Please have a look to image attached, since I'm not really sure how to explain it in a proper - understandable way. Thanks.

    Hadrest Excel on The Planet.png

    Thanks in advance for your help,

    John

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two tables - keep only duplicates ...

    This small example should show you how to create a table from the duplicates found in another table.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    06-08-2014
    Posts
    7

    Re: Compare two tables - keep only duplicates ...

    Thanks 'newdoverman' for your reply. Sorry, but I'm not getting it yet how to generate D column in your example from the column G. Afterwards everything would be clear. Thanks.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare two tables - keep only duplicates ...

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Table1
    -----
    -----
    -----
    Table2
    -----
    Table3
    -----
    -----
    2
    Item1
    87
    2
    Item2
    Item2
    33
    95
    3
    Item2
    33
    95
    Item6
    Item6
    11
    9
    4
    Item3
    13
    90
    Item8
    Item8
    55
    51
    5
    Item4
    14
    32
    6
    Item5
    45
    45
    7
    Item6
    11
    9
    8
    Item7
    51
    79
    9
    Item8
    55
    51
    10
    Item9
    65
    42
    11
    Item10
    94
    54


    Enter this array formula** in G2:

    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(MATCH(A$2:A$11,E$2:E$4,0)),ROW(A$2:A$11)),ROWS(G$2:G2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Enter this formula in H2 and copy across I2:

    =IF($G2="","",VLOOKUP($G2,$A:$C,COLUMNS($H2:H2)+1,0))

    Select G2:I2 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Compare two tables - keep only duplicates ...

    Try this Array Formula in G2. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter.)

    In G2
    Please Login or Register  to view this content.
    and then drag across to I1 and down until you get the blank cells.
    Is this what you are trying to achieve?
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  6. #6
    Registered User
    Join Date
    06-08-2014
    Posts
    7

    Re: Compare two tables - keep only duplicates ...

    Quote Originally Posted by Tony Valko View Post
    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER)
    This is awesome tip! Thanks 'Tony Valko' for your input, I understand it now

    I really appreciate your help all, you did a great job Thanks!

  7. #7
    Registered User
    Join Date
    06-08-2014
    Posts
    7

    Re: Compare two tables - keep only duplicates ...

    Thanks 'sktneer' that was exactly what I tried to achieve. Was pleasure to learn something new

    It takes a bit a time to drag across 25,000 rows but not really a big deal in this case. Thanks!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare two tables - keep only duplicates ...

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Compare two tables - keep only duplicates ...

    Glad I could help. Thanks for the feedback.
    After making the thread as solved, you may also click on * (star) to all who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two tables - keep only duplicates ...

    Column D is counting how many times the items in the second table appear in the first table. That results in a 0 or 1 in this case. The 1's are what represent a duplicate between tables 1 and 2.

  11. #11
    Registered User
    Join Date
    06-08-2014
    Posts
    7

    Re: Compare two tables - keep only duplicates ...

    Thanks 'newdoverman' I got it. Just took some time for me to understand it

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two tables - keep only duplicates ...

    You're welcome. Thanks for the feedback.

    This kind of problem shows a great thing about Excel and that is that there is more than one way of solving a problem and still be correct

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare two tables - keep only duplicates ...

    Quote Originally Posted by newdoverman View Post
    there is more than one way of solving a problem and still be correct
    Yeah!

    =SUM(A1:A5)

    =SUM(A1+A2+A3+A4+A5)


+ 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. find duplicates across two different tables
    By castleres in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 02-27-2014, 03:48 PM
  2. Identify and remove duplicates in 2 different tables
    By psychson in forum Excel General
    Replies: 1
    Last Post: 11-07-2013, 07:08 PM
  3. [SOLVED] How to Sum across duplicates (not using pivot tables)
    By derrickk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2013, 02:30 PM
  4. Highlight duplicates across two sheets or tables
    By Usta in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-18-2012, 07:52 PM
  5. Compare and extract duplicates
    By grouchmax in forum Excel General
    Replies: 7
    Last Post: 05-05-2009, 12:56 PM

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