comparing 2 sheets

K

keyur

hi!

k, this is wht i have
sheet1 (short list of about 400 parts with no descripiton)
columns - part # description

sheet2(complete list of about 2000 parts with description)
same columns - part # description


now is there a way that excel can compare a part # from
sheet 1 to that of sheet 2 and then fill the corresponding
description.

if you can help will save me hours of boring work.

thanks a lot!!
 
K

KEYUR

THANKS A LOT

I DID THAT BUT NOW, I THOUGHT THAT I HAD A COMPLETE LIST
BUT ITS NOT. ITS JUST BIG. NO WHEN IT DOESNT FINS A PART #
IT GIVES ME #N/A. IS THERE A WAY TO THAT IT WILL LIVE A
BLANK THERE.

ALSO IN SOME PART # IN SHEET 2 THERE IS NO DESCRIPTION. SO
IT GIVES A VALUE 0. CAN I GET JUST BLANK INSTEAD.

THANKS
 
F

Frank Kabel

KEYUR said:
THANKS A LOT

I DID THAT BUT NOW, I THOUGHT THAT I HAD A COMPLETE LIST
BUT ITS NOT. ITS JUST BIG. NO WHEN IT DOESNT FINS A PART #
IT GIVES ME #N/A. IS THERE A WAY TO THAT IT WILL LIVE A
BLANK THERE.

ALSO IN SOME PART # IN SHEET 2 THERE IS NO DESCRIPTION. SO
IT GIVES A VALUE 0. CAN I GET JUST BLANK INSTEAD.

THANKS
Click to expand...


Hi keyur
use the following
=IF(ISNA(VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2,
FALSE)),"",VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2, FALSE))
to filter out #N/A

If you also want to filter out blank descriptions use
=IF(ISNA(VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2,
FALSE)),"",IF(VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2, FALSE)="","No
description available",VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2, FALSE)))

HTH
Frank
 
K

keyur

thank you so much.and I WAS NOT YELLING(joking!!) my caps
lock was on and i didnt bother turning it off.
 
B

Beto

keyur said:
sheet1 (short list of about 400 parts with no descripiton)
columns - part # description

sheet2(complete list of about 2000 parts with description)
same columns - part # description

now is there a way that excel can compare a part # from
sheet 1 to that of sheet 2 and then fill the corresponding
description.
Click to expand...

Use the VLOOKUP function in the description cell of sheet1.

Assuming Sheet1 and Sheet2: ColumnA=Part# ColumnB=Description
and A2:B2000 is the range of data in sheet2,

In column B of sheet1 (Row 2 as example) you put:

=VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,0)

then copy paste this formula downwards.

Regards,
 
K

keyur

sorry just out of curiosity, what if i have more than 2
columns with only part # to compare so say
part # description1 description2
descripion3......

what would i have to add to the formula to fill them in too
 
F

Frank Kabel

Do you want these descriptions in seperate columns. If yes, just change
the third parameter (column index) of the VLOOKUP formula and insert
this into a new column. e.g.

=IF(ISNA(VLOOKUP(A2,'sheet
2'!$A$1:$B$9999,3,FALSE)),"",IF(VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,3,
FALSE)="","No
description available",VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,3, FALSE)))
gives you the third column

HTH
Frank
 
P

Peter Atherton

Just a point when using VLOOKUP the parts must be arranged
in alphbetical order. If they are not sorted then MATCH
needs to be included in the formula.

See help on MATCH

Regards
Peter
 
F

Frank Kabel

Hi Peter

I think is this case an alphabetic order is not requiered, as the
formula
=VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,0)
uses '0' or FALSE as 4th parameter. This searches for an excat match
and sorting is not requiered. Only if this parameter is TRUE you have
to sort the lookup table (ascending order)

Frank
 
B

Beto

KEYUR said:
THANKS A LOT

I DID THAT BUT NOW, I THOUGHT THAT I HAD A COMPLETE LIST
BUT ITS NOT. ITS JUST BIG. NO WHEN IT DOESNT FINS A PART #
IT GIVES ME #N/A. IS THERE A WAY TO THAT IT WILL LIVE A
BLANK THERE.

ALSO IN SOME PART # IN SHEET 2 THERE IS NO DESCRIPTION. SO
IT GIVES A VALUE 0. CAN I GET JUST BLANK INSTEAD.
Click to expand...

Ok, there is no need to YELL! (UPPERCASE is interpreted as yelling in
usenet)

change the formula to this:

=IF(OR(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,0)),
ISBLANK(VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,0))),
"",VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,0))

Maybe you'll need to check ISNA and ISBLANK, as I use ESNOD and ESBLANCO
(spanish excel). ISNA will check if the returned value is #N/A and
ISBLANK check for blank cell.

Regards,
 
B

Beto

keyur said:
sorry just out of curiosity, what if i have more than 2
columns with only part # to compare so say
part # description1 description2
descripion3......

what would i have to add to the formula to fill them in too
Click to expand...

VLOOKUP(arg1,arg2,arg3,[arg4]) has 4 arguments (last one is optional).

arg1 = cell to be matched.
arg2 = Matrix where the first column has the cell to be matched.
arg3 = Column to be returned.
arg4 = true or false (0 or 1) if the list is ordered or not (default=1).

Now if you have a wider matrix, you just tell wich column number to be
returned in arg3. If description2 is in column 10 of the matrix, replace
the 2 in the previous formula by a ten. Each formula will return just
one field, so if you want more than one description you'll need to copy
the formula and change it to suit your needs.

Regards,
PS: This is explained in the help file (I suppose it should).
 
B

Beto

Peter said:
Just a point when using VLOOKUP the parts must be arranged
in alphbetical order. If they are not sorted then MATCH
needs to be included in the formula.
Click to expand...

No it doesn't, that's what the fourth argument is for, 0 = not ordered,
1 = ordered.

Regards,
 
Top