If Duplicats in col A, I need differences in other colls to be highligted

Hi there,
I might be making life harder then it is, but I cant find an answer to my "problem", and would really appreciate someones help.

I've got a table which download information from our business application through an ODBC on daily basis.
The table keep in growing, and a macro will transfer the results into another tab - where duplicates are removed.
Duplicates are only removed if all the cells are equal to an earlier row - and this is important for me!

Now comes the thing, In case there are duplicates in Col A - they are formatted with CF, so that the cells become red.
Now I'd like the file to highlight the information which is different between these two lines - how can that be done?

Sample below;

MSCUTY06 2013-01-04 TURBO HELSINGBORG Casbada PL301R
MSCUTY04 2013-01-10 TURBO HELSINGBORG Casbada PL302R
MSCUEC50 2013-01-15 TURBO HELSINGBORG Casbada NQ302R
MSCUTY06 2013-01-04 TURBO HELSINGBORG Cascada PL301R

A1 & A4 are duplicates - so both of them are to be shown as red - which is good
E1 & E4 are different - and I'd like Excel to highlight "HERE IS THE DIFFERENCE"!

How can that be done?

First find duplicates i Col A
Then highlight what are the differences between the two rows?

Many thanks for your kind comments.

B regards
// J0ck3

In order to compare data, it should be sorted. Here's the table based on your example:

MSCUEC50	15/1/2013	TURBO	HELSINGBORG	Casbada	NQ302R
MSCUTY04	10/1/2013	TURBO	HELSINGBORG	Casbada	PL302R
MSCUTY06	4/1/2013	TURBO	HELSINGBORG	Casbada	PL301R
MSCUTY06	4/1/2013	TURBO	HELSINGBORG	Cascada	PL301R

Assuming that your data is sorted by columns A and B ascending, highlighting should be achieved by inserting the conditional formating in the columns you wish to be highlighted (define it in cell B2, and use format painter on the remaining cells):

=IF($A2=$A1;B2<>B1;IF($A2=$A3;B2<>B3;FALSE))

Hope this helps

Hi, and thanks for comment.

Still, let's assume that I will have 2-5 maybe even 6 rows where value in col "A" would be equal (A1,A4,A7 & A9).
If i sort it, these will be added in a pritty order on top of eachother - yes.

But how does Excel know which line is my first input (the original line?)
When using "remove duplicates" the original / first created line is always kept - and this is what should be compared with the new lines..

What if values in B,C,D,E or even ZZA (on different rows) are different from eachother?
Line 1,2& 3 are identical, but 4th line is different from 1st line?

The sample I sent you is only the first few columns, my real file has aprox 35 columns - so there is a lot of data to compare.

Let's say that i've got a hundred new rows / day, and some will have duplicates values in col A, while others wont.
If I have to monitor those/find these on my own - I'd rather quit my job

I need the format to start by identifying the rows with the same value in col A, and then let me know what is the difference in between the lines.
(no matter how many lines there are to compare)

Is it possible or not?

I don't know how excel knows which is the original row, and frankly, I don't believe it does. My guess is it selects the first row found, no matter where it was before -let's say- a sort operation. I wouldn't rely too much on excel2010 automations if I were you, as it has been quite often proven to be tragically wrong.

As for the VISUAL format of the cells, the formula provided will work, no matter how may rows or how many columns. As you may have noticed, assuming the item to be compared is in column A, the cell in column B -for example- is compared with its above (B2<>B1), when $A2=$A1, and with the cell below it (B2<>B3), when $A2=$A3. The $ before the column id ($A) states that column A will allways be used in the formula, whereas no $, as in B2, states that copying the formula, will adjust the column id.

So, if you FormatPaint cell B2 to cell C2, the formula will be
=IF($A2=$A1;C2<>C1;IF($A2=$A3;C2<>C3;FALSE)).

The same applies to line ids, since no $ in any line id, as you apply the format in all your range, it will adjust accordingly.

Hi, and thanks again.

I'm currently trying it - but not sure If im doing it correctly... :/

I guess that the formula must be applied inside "Conditional formatting" - right?
If not, how do I apply a formating code, which does not overwrite the contect of the cell?

I did apply as "conditional formatting", but now I'm unsure how to "drag" the formula to other cells.
Is there a key trick, how to spread coditional formatting which I'm not aware of?

Or, do I have to install/apply this rule for all the cells in my spreadsheet, one-by-one?

JOck3 Hi!

First of all, I am sorry that I didn't see your post earlier.

The formula I posted is not to be entered in a cell, it is to be entered in the Conditional Formatting property of the cell! So, the steps are:
- On the Home tab, in the Styles group, click the Conditional Formatting button.
- Click More Rules on the drop-down
- The one we want is the second option, "Format only cells that contain"
- In the first drop-down in the Rule Description -it should show "Cell Value"- select "Formula"
- Enter the formula I provided and press OK

Then find a button showing a paintbrush (on the Format ribbon?) and -while on the cell that's been conditionally formatted- click it, then click on the cells that you want to be similarry formatted.