comparing 2 sheets
 

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!!

comparing 2 sheets
 

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

comparing 2 sheets
 

KEYUR wrote:
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


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

comparing 2 sheets
 

thank you so much for the help

comparing 2 sheets
 

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

comparing 2 sheets
 

keyur wrote:

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.

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

comparing 2 sheets
 

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

comparing 2 sheets
 

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


keyur wrote:
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

comparing 2 sheets
 

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
-----Original Message-----
keyur wrote:

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.

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

.

comparing 2 sheets
 

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


Peter Atherton wrote:
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
-----Original Message-----
keyur wrote:

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.

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

.

comparing 2 sheets
 

KEYUR wrote:

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.

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

comparing 2 sheets
 

keyur wrote:

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

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).
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

comparing 2 sheets
 

Peter Atherton wrote:

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.

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

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.