Compare 2 excel worksheets in a new sheet with criteria

**barco** · 06-20-2012, 06:39 AM

Hi guys

Problem. I have imported 2 CSV's in excel. Each CSV in a separate sheet. One contains about 20000 records the other one about 15000. A lot of diffs.
I want to create a third sheet where the first CSV is lined up next to the other CSV and with a sort on the first 3 columns of each CSV. I can send you the excel with both CSV's in a separate sheet if needed.

To put this in an example:

CSV1:
A B C D (columns)
a b c d
a b c e
a b d d
a c c d

CSV2:
A B C D (columns)
a b c d
a b c b
a b d d
a b c e

Result in third sheet:

A B C D E (=A CSV2) F(=B CSV2) G(=C CSV2) H(=D CSV2) columns I (result)
a b c d a b c d equal
a b c e a b c e equal (same entry in CSV2 is on 4th row, so it must also search to equal rows and line them up)
a b d d a b d d equal
a c c d - - - - new (- = empty cell)
- - - - a b c b new (- = empty cell)

If you can help me out with some kind of formula that would be great !

Thanks in advance

Bart

**arlu1201** · 06-20-2012, 06:46 AM

It will be good if you attach a sample file so we can understand your requirement.

Also, have a sample output sheet to show us how you want the output to be shown.

**barco** · 06-20-2012, 08:21 AM

Hi

I attached an example. First 2 sheets are the data to compare. Third sheet must contain the comparison and a result column. Different results are possible determined by the comparison.

Diffs are highlighted in green.

I like to have a formula or macro that fills the third sheet with this information and automatically gives a result and highlights the diffs. Pretty complex I guess to do.example.xls

Thanks in advance.

Bart

**barco** · 06-21-2012, 10:32 AM

Anyone else perhaps that can help me out ?

**arlu1201** · 06-21-2012, 10:59 AM

So each column in CSV1 should be compared to each column in CSV2?

Also which is the unique id / column based on which the files should be in sync (on the same row)?

**barco** · 06-21-2012, 11:58 AM

What I want is that all the data in CSV1 is compared with the data in CSV2 on record (row) level with the exception of some columns that are not needed and make every record unique. In the result sheet you can see which columns are needed and not needed.

So to make a summary:
- I import CSV1 to sheet1 and sort it by column A,B,F,G,H,I,K,L,M
- I import CSV2 to sheet2 and sort it by column A,B,F,G,H,I,K,L,M

Than in a third sheet I want both CSV's next to eachother (not every column only the ones that are in result sheet (see example).

He has to put the rows that are exactly the same next to eachother and set a value (e.g. equal) in a cell next to the compared record (new column). Also the third sheet should be sorted the same way as sheet 1 and 2. CSV1 or 2 can contain records that are not in the other CSV so an empty record next to it is expected in the third sheet with a result (e.g. new) next to it.

That's about it :-) Complex I think no ? Thanks for looking into my problem.