Results 1 to 4 of 4

Thread: how can i highlight cells with color, which contain duplicates form a separate excel sheet

  1. #1
    Join Date
    May 2010
    Posts
    2

    how can i highlight cells with color, which contain duplicates form a separate excel sheet

    I have one query regarding the Microsoft excel. I have one excel file with multiple sheets.

    What i want to do is -

    - Highlight any values (in colour) on the current sheet that I'm in
    - which contains a duplicate from the previous sheet

    Help would be appreciated!!

  2. #2
    Join Date
    Dec 2007
    Posts
    996

    Re: how can i highlight cells with color, which contain duplicates form a separate excel sheet

    Which version of Excel are you using, if it is excel 2007 then follow the below procedure:

    1. First you need to highlight the cells/rows that you wish to work on.
    2. Then select conditional formatting from the ribbon toolbar
    3. After that highlight rules/duplicate values
    4. Then Set the colour you wish.
    5. Just click Ok and apply.


    You can also refer to similar topic here: How can I highlight duplicate rows of excel sheet with color?

  3. #3
    Join Date
    May 2010
    Posts
    2
    Yes that works for duplicates within the page but i want the cell to change colour if the data is duplicated on the previous worksheet

    For example i would like ITEM NO 4-011511 to be highlighted due to it being a duplicate on the previous worksheet

    Sheet 26-06-10

    ITEM NO MACHINE SETS SETUP
    4-011511 Eroda 5 0.00 - highlight
    4-012852 Eroda 1 1.50
    2-012293 Top Gun 1 0.00
    2-005330 Drake 1 0.00
    4-0128047 Matrix 1 1.50
    4-012850 Matrix 1 1.50 - highlight

    Sheet 25-06-10

    2-005330 Drake 1 1.50
    2-012293 Top Gun 1 1.00
    4-012707 Top Gun 1 1.50
    4-012852 Eroda 1 1.50
    4-011511 Eroda 6 1.50
    2-012576 Top Gun 1 1.00
    4-012850 Matrix 1 1.50

  4. #4
    Join Date
    Dec 2007
    Posts
    1,602

    Re: how can i highlight cells with color, which contain duplicates form a separate excel sheet

    You really cannot sort a column on the basis of a conditional format. If you want to sort the data so that the duplicates are in one area, follow these steps:

    First, type the heading Duplicate in cell B1. Then, type this formula in B2:

    =COUNTIF(A:A,A2)>1

    With the cursor in cell B2, double-click the AutoFill handle (the little square in the lower-right corner of the cell) to copy the formula all the way down the column.

    You can now sort the columns by column B (descending), then by column A (ascending), to show the duplicate invoice numbers at the top of the range.

Similar Threads

  1. Replies: 2
    Last Post: 01-02-2012, 01:08 PM
  2. How to find total number of duplicates from lists of sheet in excel
    By Kungfu Pandey in forum MS Office Support
    Replies: 2
    Last Post: 08-01-2012, 03:51 PM
  3. Exporting Project files in form of Excel Sheet
    By aelmalki in forum Microsoft Project
    Replies: 10
    Last Post: 20-04-2011, 07:49 PM
  4. Replies: 3
    Last Post: 27-11-2010, 04:25 AM
  5. How can I highlight duplicate rows of excel sheet with color?
    By Sarfaraj Khan in forum Windows Software
    Replies: 5
    Last Post: 08-01-2010, 10:15 AM

Posting Permissions

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