Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing 2 sheets
thank you so much for the help
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing 2 sheets | Excel Discussion (Misc queries) | |||
Comparing 2 sheets | Excel Discussion (Misc queries) | |||
Sheets comparing | Excel Worksheet Functions | |||
comparing 2 sheets | Excel Discussion (Misc queries) | |||
comparing sheets | Excel Programming |