Excel Help

dave13

Hi
I have two sheets of data.In cell A in both ther are just numbers, and in cell b descriptions. I need to match off the numbers and see if the descriptions are the same for the relevant number. Is there any automatic way of doing this in excel as i don't really want to manually go through about 1,000 entries?
Any help much appreciated.

seamus

You probably want a VLookup, but just to verify:

Sheet 1, has a list of values (say 1 to 100) in column A, and the corresponding text value in column B.

Sheet 2 has a random list of numbers in column A, and what is supposed to be the corresponding text in column B.

So for each value in Sheet 2!A (Sheet 2, col A), you want to check the text in column B against the list in Sheet 1. For example, if Sheet 2, A1 has the number 12, then you want to scan down to the number 12 in Sheet 1 and check the text beside it?


Or do you just want to check that the two sheets are identical?

dave13

Sorry to clarify
Columm A in both sheets have most of the same numbers in common(one sheet has extra numbers).For the one's that are common i want to find out if all the corrosponding descriptions in B are the same?
Does that clarify the problem?

seamus

Yeah, that's an easy one.

Assuming that the first Sheet is called "Sheet1", in the second sheet, in Cell C1, put in the below formula:

=IF(A1=Sheet1!A1,IF(B1=Sheet1!B1, 0, 2),1)

This formula will put a number in cell C1, depending on the result of the test:
0 - Both sheets match
1 - The numbers in Cols A don't match
2 - The numbers in Cols A do match, but the text in Cols B doesn't.

You can then apply this formula to all cells in column C (highlight, and double click on the bottom-right corner of C1).

dave13

Seamus
The numbers in column A aren't in the same rows in column b because of a load of extra numbers in sheet one. I need to be able to compare the number s and descriptions without the numbers being in the same row. Sorry for the hassle, been trying to use VLOOKUP. Using "=VLOOKUP(F98,Sheet1!A1:B1120,2,FALSE)" i can retrieve the other description and compare but i'm cutting and pasting the value into F98 each time. it ain't a very quick way.

seamus

OK then, first things first, you'll need to sort the data in sheet one by column A.

Then you need to define a name for the data in sheet one. Select A1 in sheet one, and press Shift + Ctrl + End. This should select all of the data from A1 - Bx. Give it a name - Insert -> Name -> Define. Call it "ref_data", click Add, then OK.

Then in Sheet 2, C1, insert the below formula.
=VLOOKUP(A1,ref_data,2,FALSE)

Apply this to all of Column C.
Now you should have the two comparable strings in cols B and C. Apply an IF function to column D to highlight the mismatches.

dave13

Thanks for that seamus.That works great, saves a lot of repetitive work.