Previous Topic Next Topic

Comparing 2 spreadsheets

Closed
bumblebee - 28 May 2010 à 07:23
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 31 May 2010 à 12:29
Hello,

I need to compare 2 spreadsheets that are coming form different sources but have mainly the same content. I can easily edit them so that the columns are in the same positions in both but I need to know how to compare each column of one spresheet to the corresponding one on the other.
Basically, to compare things such as stock codes, barcodes and price to make sure they are all the same on both sheets. One list will contain items that the other does not.
I need it to return items that do not match
Appreciate any help on tis

Related:

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
28 May 2010 à 07:39
How many columns are we talking about here
Also on a single row, if you add up all the cell how many maximum characters would be there
bumblebee
28 May 2010 à 07:46
8 columns, about 40 characters would be the maximum but no telling it won't be more in the next report. I've been reading a lot about macros but don't have much experience. Would I be better off doing separate macros for each column?
Maybe create a spreadsheet and import the two files, have the macros in place. I would like to create a quick method of comparing this data as it will have to be done twice a week
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
28 May 2010 à 08:07
It the total charactes in a row would be less than 255 then you can try this


In a new column Add a new column using formula that combine all columns into one and then use a match function

Let say you have two sheets, Sheet1 and Sheet2 and you want to compare columns A-H

Then on column J add this formula

=A1 & "|" & B1 & "|" & C1 & "|" & D1 & "|" & E1 & "|" & F1 & "|" & G1 & "|" & H1

and drag it down to last row

Do same on the other sheet


Now in column K on Sheet1 add this formula

=IF(ISERROR(MATCH(J1, Sheet2!J1:J65536,0)), 0, MATCH(J1, Sheet2!J1:J65536,0))

Now filter on 0. These are the values that do not match
bumblebee
28 May 2010 à 08:57
Thank you, unfortunately, this returned everything as 0. I can relook at the formatting but as the information comes from 2 different sources it is possible that the information is presented in a different way, such as product code 12-34 as opposed to 1234.
That is a very useful formula that you gave me though, must write it down.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
28 May 2010 à 09:42
You may have to massage the data before one can do a compare. Not knowing how the data can be, hard to tell you. Like 12-34 might be a valid value as well as 1234

Lets say that dash or space you can substitute function like

=SUBSTITUTE(TRIM(A1), "-", "") & "|" & SUBSTITUTE(TRIM(B1), "-", "") & "|" & SUBSTITUTE(TRIM(C1), "-", "") & "|" & SUBSTITUTE(TRIM(D1), "-", "") & "|" & SUBSTITUTE(TRIM(E1), "-", "") & "|" & SUBSTITUTE(TRIM(F1), "-", "") & "|" & SUBSTITUTE(TRIM(G1), "-", "") & "|" & SUBSTITUTE(TRIM(H1), "-", "")


It first remove any leading or trailing space and then remove any "-"

This is just an example. Of course it was to just get the ball rolling
bumblebee
28 May 2010 à 11:00
Yeah it's great thank you, the main problem I have from looking at the data is that the same data might occur in both sheets but not on the same line.
It looks like I may need to do a search for each individual value on one sheet to compare it to the column with the same heading in the other sheet and if it does not match, get it to return the value, and then continue along the row.
Forgive my ignorance, I have never had to do this kind of thing before! I'm beginning to think that comparing the data manually might be easier and faster!