How To Compare 2 Spreadsheets?

[byepeeps]

Hi,

I have two sets of spreadsheets. One has new statistics the other has older statistics. The newer spreadsheet has additional rows. What I want to do is remove the newer additional rows so I can compare like with like.

Here's an example

S/sheet1
Column C
word1
word1
word1
word2
word3
word3

S/sheet 2
Column C
word1
word1
word1
word8
word
word2
word3
word3
word90

As you can see s/sheet 2 has additional rows but also rows which match spreadsheet 1. I want to remove the entire rows in spreadsheet 2 that don't match spreadsheet 1, without affecting the other columns of data associated with the rows.


Does any of that make sense? If so, how do I do this?

Thanks,
Alex

[NBVC]

Does it matter how many times a word is repeated? You have words repeating in both sheets it seems.

[byepeeps]

Does it matter how many times a word is repeated? You have words repeating in both sheets it seems.

Unfortunately it does and that's the real pain isn't it? The reason is because each word has different 'match types' which are given in a separate column.

[NBVC]

So can you use the combination of the 2 columns (assuming that is unique) and compare with a combination in the other sheet?

[byepeeps]

I'm not sure how you mean. Would it be easier if I attached the spreadsheets?

[NBVC]

Yes it always is....

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[byepeeps]

Hi,

The dummy spreadsheet is now attached with most of the data deleted for privacy. It has two worksheets (old data and new data).

Does this help?

[NBVC]

So now what columns are we comparing?

[byepeeps]

Ah sorry. The keyword column is the one we're comparing.

[NBVC]

Hopefully this can do it...

in AA2 of New Data enter:

=ISNUMBER(MATCH(1,INDEX(('Old Data'!$C$2:$C$10='New Data'!C2)*('Old Data'!$D$2:$D$10='New Data'!D2),0),0))

I used the Keyword and Keyword Status to determine uniqueness....

You need to adjust the ranges for Old Data to suit...

and copy down...


This will give TRUE for matches found and FALSE for not found.

Then you can Data|Filter|Auto Filter for False in AA and delete those rows.

[byepeeps]

Thanks for that it seems to work to some extent but doesn't give me all the matches. I can see one particular keyword on both sheets but it' coming out as FALSE.

[NBVC]

Is that in the sheet you attached or in your main sheet?

[byepeeps]

Scratch that. All sorted. Thanks for your help.

Much appreciated.

Alex