Comparing lists of data

[Confused]

Hi there,

I have a list of data in sheet 1. I have a second list of data in sheet 2,
which contains all of sheet 1 plus some new entries. I want to compare the
two sheets and highlight only the new entries. Any ideas?

Thanks

 

[Ashish Mathur]

Hi,

Let's say you have two sheets - Sheet 1 and Sheet 2. On sheet 1, data is in
range A1:A10 and on sheet 2, data is in range A1:A25. Please perform the
following steps:

1. Name the range A1:A25 on sheet 2 as "Compare";
2. Now on cell A1 of sheet 1, go to Format . Conditional formatting" and in
the left had side drop down, select Formula is;
3. In the formula box, type the following formula -
=vlookup(A1,Compare,1,0);
4. Click on the Format tab and select the colour of the font you want.

Hope this helps.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Confused]

Hi Ashish,

Thanks for the help, but it didn't work. I even tried swaping it around and
nothing happened.

Sheet 1 Sheet 2
A Dart & Co A Dart & Co
A Dart & Co A Dart & Co
A Dart & Co A Dart & Co
A Dart & Co A Dart & Co
A Dart & Co A Dart & Co
A Noble & Son Ltd - QLD Division A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd - QLD Division
A Noble & Son Ltd Vic Division
A Noble & Son Ltd Vic Division
A Noble & Son Ltd Vic Division
A1 Asphalting Pty Ltd
A1 Distribution
A1 Distribution
AAA Engineering Technologies Pty Ltd
AAH Contracting Pty Ltd
ABB Australia Pty Ltd - NSW

If your formula had worked everything in sheet 1 should have been
highlighted, as they are all in sheet 2 - is that correct? If that is
correct, it didn't work....

Any other idea's?
Thanks

 

[Max]

An alternative play is to use INDIRECT within the CF formula to apply it
across sheets. Going by the sample data you posted, you want to compare items
in Sheet1 with those in Sheet2, and to colour new items in Sheet2 (if any)

Assume items are listed in col A in both sheets
In Sheet2,
Select col A (A1 active), then apply CF using Formula Is:
=AND(A1<>"",ISERROR(MATCH(A1,INDIRECT("'Sheet1'!A:A"),0)))
Format to taste > OK out
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---

 

[Ashish Mathur]

Hi,

May I request you to mail me the workbook at (e-mail address removed).

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Confused]

Hi Ashish,

I have sent you an example.

Thanks

Hi,

May I request you to mail me the workbook at (e-mail address removed).

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Confused]

Hi Max,

Thanks for your help - it has worked. The data that is new to sheet 2 is
highlighted.

Thanks

 

[Max]

That's good. Do take a moment to press the "Yes" button (like the ones below)
in responses which help answer your query.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik