Tech Support Guy banner
Status
Not open for further replies.
1 - 12 of 12 Posts

·
Registered
Joined
·
78 Posts
Discussion Starter · #1 ·
i have 2 excel documents that i need to check the data of, and confirm that 2 collumns are the same on both.

can somebody please point me in the right direction as to how i can go about doing this please as at the moment i am doing it manuelly and it is proving to be a drag.

can i run an electronic comparison???

Thanks in advance
 

·
Registered
Joined
·
78 Posts
Discussion Starter · #2 ·
for example.........

on one work book i have

part no. QTY
300124 5
3001125 1
300116 9


on the second work book i have

part no. QTY
300124 5
3001125 2
300116 9
301245 2

so obviously thebelow are different and are highlighted to bring attention to this

3001125 2
301245 2

i have heard of vlookup etc but not really used it to know what to do
 

·
Registered
Joined
·
7,762 Posts
I would usually cut and paste the 2 columns onto a new sheet, and in the next column use =exact(a1,b1) and then copy it down the entire length

It will display a False if the 2 cells do not match exactly.
 

·
Registered
Joined
·
7,762 Posts
Hiya Drew;):D:up:


and good timing!
 

·
Registered
Joined
·
436 Posts
It would probably be easier to do what RandyG said about copying and pasting the columns to a new workbook, then use the Exact function to simplify the whole thing.

drew
 

·
Registered
Joined
·
7,762 Posts
You arer looking to compare 2 cloumns of data to see if they are the same, or where there are differences.

Open both files where the columns are that you want to compare.

copy each colum to a new sheet, and in the next colum, paste the code I have above, and copy it to each cell you want to compare.


So clumn A would have the column from spreadhseet 1, and Column B would have the column from Spreadsheet 2, and in column C you would have:
=EXACT(A1,B1)
=EXACT(A2,B2)
=EXACT(A3,B3)
=EXACT(A4,B4)
=EXACT(A5,B5)
=EXACT(A6,B6)
=EXACT(A7,B7)
etc . . .


If the result is "True" then the cell is the same

If the result is False, then the cells are different, and you will have to go in and manually chack for the differences.

the only thing this is going to do is give you a quick check to see which ones are different, which is what you asked.
 

·
Registered
Joined
·
2,702 Posts
Are you stuck blueboy75?

If I read it right, you want to flag up anomalies in wb2.

Assuming that the lists are in columns A and B of Sheet1 of the respective books, and that A1 and B1 contain the labels ("part no" and "QTY") ...

With both wbs open, try this formula in C2 of wb2 ;

=B2=INDIRECT("[Book1]Sheet1!B"&MATCH(A2,[Book1]Sheet1!$A:$A,0))

NB - there are no spaces in this. It should give you #N/A where the part no. cannot be found in wb1, and FALSE where part no. exists in wb1 but QTY doesn't match.

HTH,
Andy
 

·
Registered
Joined
·
7,762 Posts
I missed blueboy75's 2nd post. I think that both Drew and I missed it, as we posted at the same time, and when I first saw this thread, there was only one post in it.

Thanx for stepping in Andy!!;):D:up:
 

·
Registered
Joined
·
78 Posts
Discussion Starter · #12 ·
i have it sussed!! after playing around with it Thankyou V Much guys!!

i have created a new W/book and have added in the formula =exact etc. for the relevant cells, and it works, the only time it doesnt work is when the workbook sorting does not sort it correctly, but that is another story.

thanks on this one.
 
1 - 12 of 12 Posts
Status
Not open for further replies.
Top