Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 31-05-2010
Member
 
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!!

Reply With Quote
  #2  
Old 31-05-2010
Member
 
Join Date: Dec 2007
Posts: 998
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?
Reply With Quote
  #3  
Old 01-06-2010
Member
 
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
Reply With Quote
  #4  
Old 01-06-2010
Member
 
Join Date: Dec 2007
Posts: 1,586
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.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "how can i highlight cells with color, which contain duplicates form a separate excel sheet"
Thread Thread Starter Forum Replies Last Post
Not able to change font color in some cells of Microsoft Excel Garsen's MS Office Support 2 01-02-2012 01:08 PM
How to find total number of duplicates from lists of sheet in excel Kungfu Pandey MS Office Support 2 08-01-2012 03:51 PM
Exporting Project files in form of Excel Sheet aelmalki Microsoft Project 10 20-04-2011 07:49 PM
Cannot highlight distinct cells in different rows and column in a Pivot table ? Grayson Windows Software 3 27-11-2010 04:25 AM
How can I highlight duplicate rows of excel sheet with color? Sarfaraj Khan Windows Software 5 08-01-2010 10:15 AM


All times are GMT +5.5. The time now is 08:27 AM.