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

[guigey]

Sponsored Links

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!!

[SUpER CoP]

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?

[guigey]

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

[Expertz]

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.