excel spreadsheet comparison

[loganswell]

Hello,

If I have two excel speadsheets and wish to make comparisons between the two how do I do this?

I know that this subject was covered very recently with someone wanting to know how to compare 3 worksheets but it was more complex (well maybe) and I found the answers complex too.

Specifically, here is my query:

One sheet contains a list of over 5000 items. The other sheet contains pretty much the same list but with additions and subtractions (new items and items removed).

I need a new sheet that perhaps has two columns identifying new items added and old items removed.

Is there a quick, simple way to do this? I don't think I have MSQuery (as was mentioned in the 3 sheet problem) however I CAN port this data into Access using csv files if that would help any.

I might add that I have managed to achieve my goal (simply because it HAD to get done that day) however I feel that the method that I employed was somewhat cumbersome to say the very least.

[I coloured the background of the cells in sheet A one colour and sheet B another. I then pasted B on to A at the foot of A and then did an A-Z sort of the column. The lack of continuity of the colours was enough for me to quickly identify additions and subtractions. Like I said - prolly a bit Heath Robinson but it worked]

I look forward to hearing if there is a more scientific method that may be applied for the next time that I do this.

Many thanks,

Jim

 

[ETID]

This would be a more automatic / setup one time solutiuon.


I took somewhat the same approach that you did, but using conditional formatting instead


First I named the ranges/columns that I want to compare...
List_1 and List_2 respectivley highlite the column, then from menu (Insert>name,define...) I named mine List_1 and then the second, List_2

then

highlighting the first list...
then using conditional formating (Format>Conditional formating)...

I selected the "formula is" from the first drop down and typed this in the formula box =if(iserror(match(A1,List_2,0)),true,false)...set a format for when there is no match.

repeat for your second list..
using this formula.

=if(iserror(match(A1,List_1,0)),true,false)

Now when any item from any list is added or deleted the format will reflect if there is no a match in other.

 

[macropod]

You could do this fairly easily using COUNTIF.

For example, suppose you have two workbooks, with the data to be matched in column A of Sheet1.

In Workbook 1 you could place the formula:
=COUNTIF([Book2]Sheet1!$A:$A,A1)
in B1 and copy down for as many rows as Workbook 1 has data.

Linkewise, in Workbook 2 you could place the formula:
=COUNTIF([Book1]Sheet1!$A:$A,A1)
in B1 and copy down for as many rows as Workbook 2 has data.

Now all you need to do is to sort the data in both workbooks by column B; anything with a zero value in column B is unique to that workbook, and anything else is common to both.

Bear in mind, though, that comparisons of thousands of rows will make for a large, slow workbook.


Cheers

 

[loganswell]

I shall look at this. Thank you both very much for the replies. It looks very helpful.

Best wishes,

Jim