+ Reply to Thread
Results 1 to 6 of 6

Highlight cells of duplicate data from two spreadsheets

  1. #1
    Registered User
    Join Date
    06-13-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    4

    Highlight cells of duplicate data from two spreadsheets

    So, I have two spreadsheets that I tried to use conditional formatting to highlight duplicates, but failed.

    Data from Spreadsheet A has multiple columns but I need to compare only: Column A - order # Column C - count Column D - date

    Spreadsheet B also has many columns, but I need to compare: Column A - order # Column B - count Column E - date

    I need to highlight column A in the rows on both spreadsheets when the data across the row is identical.
    So if data in A,C D of whatever row is found to be identical in A,B,C of any row, then highlight A of that row on both sheets.

    In this example, row 6 and 14 have different dates and 7 and 15 have different quantities so they wouldn't be flagged.
    For ease of input I used only one spreadsheet, but this data is on two different spreadsheets.

    Example.JPG

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Highlight cells of duplicate data from two spreadsheets

    Dear G56:

    1. can macros be used?
    2. can there be multiple duplicates?
    3. could you facilitate the sample spreadseet as an excel file?

  3. #3
    Registered User
    Join Date
    06-13-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    4

    Re: Highlight cells of duplicate data from two spreadsheets

    Prefer not to use macros
    There could be duplicates in order and count, but no on same date.
    Cannot send file.

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Highlight cells of duplicate data from two spreadsheets

    Hello G56709,

    Welcome to the forum

    Apart from what rcm already said, I wanted to ask if you are fine with using a simple helper column for the following reasons:

    *) To do a simple Vlookup / Index Match function, returning the date of the same order, but on a different list / sheet, because:
    1. If there is no result, the order is not found in the other list, aka it is not dupplicated and
    2. If there is a result, simply compare the two date, date of that order in this list and the date found, of that order, in the other list. Different = no colour, same = colour (Much easier for conditional formating).

    And yes, as rcm said, a simple sample workbook would be much easier for others to help you too. It does not have to have the real data, you can create some dummy unique set of data just to be used as example, but preferably having the same amount of information (can be made up, but having enough rows / columns like the real data set will help constructing the formula)
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  5. #5
    Registered User
    Join Date
    06-13-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    4

    Re: Highlight cells of duplicate data from two spreadsheets

    I have attached an example of the spreadsheet. There will definitely be multiple order numbers with the same count, but not on the same day, so a vlookup didn't work for me. What would the formula be? Maybe I could insert it in a conditional format.

  6. #6
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Highlight cells of duplicate data from two spreadsheets

    If you only want to highlight the row in only one case - where an order exists in both list and somehow one of the date of that order on the 2nd list, matches the date of the same order on that row, then a Countifs / Sumifs or a Sumproduct to simply count and find such a case would help you.

    (I couldn't find your attached spreadsheet )

+ 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] Highlight cells that are duplicate pairs
    By erob223 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2015, 10:46 AM
  2. [SOLVED] Find Duplicate - Highlight Cells
    By da80th in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-15-2013, 07:49 PM
  3. Highlight cells if duplicate found on the same row
    By martinez_pedro in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-23-2010, 04:24 AM
  4. Copy and duplicate cells onto other spreadsheets
    By Mattireland in forum Excel Programming / VBA / Macros
    Replies: 40
    Last Post: 08-27-2010, 05:48 PM
  5. Highlight Visible Duplicate Cells
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2010, 10:20 AM
  6. Highlight Duplicate Cells in Different Colors
    By dbla921 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2008, 04:43 PM
  7. Highlight duplicate cells in next column...
    By ImpetuousRacer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-25-2008, 03:24 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