+ Reply to Thread
Results 1 to 6 of 6

Matching Multiple Cells in Two Tables Simultaneously

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Matching Multiple Cells in Two Tables Simultaneously

    QUESTION: How do you search two data tables simultaneously to find out the following:
    Which customer + account + amount is found in what table?
    Which customer + account + amount is found in both tables?
    Which customer + account + amount is found in neither table?

    IMPORTANT: All three values -- customer + account + amount -- must be matched.
    The goal is to employ conditional formatting so that:
    - The customer + account + amount set found in Table A is highlighted in BLUE.
    - The customer + account + amount set found in Table B is highlighted in YELLOW.
    - The customer + account + amount found in both tables is highlighted in GREEN.
    - The customer + account + amount not found in either table is highlighted in RED.

    ALSO: One table will likely contain more data than the other, ie: Table A may contain 150 records while Table B contains 200, etc.

    ALTERNATIVELY
    If conditional formatting is not workable, then maybe the result can identify the table(s) customer + account + amount set is found in, with the result cells looking something like this:
    TABLE B
    TABLE A
    TABLE A & B
    NOT FOUND

    For an example of the data sets I have in mind, please see the Google spreadsheet at the link below.

    Thank you!

    LINK TO PUBLIC SPREADSHEET:
    https://docs.google.com/spreadsheets...it?usp=sharing

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

    Re: Matching Multiple Cells in Two Tables Simultaneously

    Try this.....

    Select the range E3:G164 --> Conditional Formatting --> Make a New Rule for conditional formatting using the formulas given below and set format as per your choice.

    For Data in both the Table A & Table B
    Please Login or Register  to view this content.
    For Data in Table A only.
    Please Login or Register  to view this content.
    For Data in Table B only.
    Please Login or Register  to view this content.
    For Data neither in Table A nor in Table B i.e. For Data Not Found.
    Please Login or Register  to view this content.
    For details see the attached sheet where I have applied the conditional formatting in the lookup table range.

    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.

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Matching Multiple Cells in Two Tables Simultaneously

    Hello sktneer,

    This is absolutely beautiful! Thank you!

    One follow up question:
    Is there a formula I can also use in an adjacent column to include the text indications of which table (or lack thereof) the data was found? (Similar to what is shown on the spreadsheet I posted and the one you provided.)

    Example:
    TABLE A & B
    TABLE A
    NOT FOUND
    TABLE B

    Kind regards,

    Dave Myers

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

    Re: Matching Multiple Cells in Two Tables Simultaneously

    You're welcome and thanks for the feedback.
    For your new requirement, try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula.
    Please Login or Register  to view this content.
    and then copy down.

  5. #5
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Matching Multiple Cells in Two Tables Simultaneously

    Hello sktneer,

    Works flawlessly!

    Thank you so much!

    Kind regards,

    Dave Myers

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

    Re: Matching Multiple Cells in Two Tables Simultaneously

    You're welcome.
    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

+ 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. Change Data Source for Multiple Pivot Tables Simultaneously
    By mshirschy in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-11-2013, 02:31 PM
  2. Replies: 1
    Last Post: 06-14-2012, 04:12 PM
  3. Replies: 10
    Last Post: 06-13-2012, 09:38 AM
  4. Replies: 3
    Last Post: 02-27-2012, 08:03 PM
  5. Re: Change Multiple Page Fields Simultaneously in Pivot Tables
    By chudok in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2010, 01:29 PM

Tags for this Thread

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