| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets with pricing information
I have two spreadsheets that contain item #, descriptions, and costs. One is
from 1008 and the other is for 2009. There are 2460 rows of information in the 2008 and 3586 rows of information. I need to compare the 2 sheets and if the part # exists in 2009 then change the cost pricing in 2008 to newest cost. Also there are obviously 1186 new item # in the 2009 sheet, is there a way to have them separated if they don't match the 2008 so they can then be added at a later time? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two spreadsheets with pricing information
Suppose 2008 sheet is named as Sheet1 and 2009 as Sheet2 and Cols A,B,C in
both contain item #, descriptions, and costs respectively. In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of Sheet2 =VLOOKUP(A1,Sheet1!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)),"Not found in 2009","Found in 2008") wiil give you more meaningful message... "sta1" wrote: I have two spreadsheets that contain item #, descriptions, and costs. One is from 1008 and the other is for 2009. There are 2460 rows of information in the 2008 and 3586 rows of information. I need to compare the 2 sheets and if the part # exists in 2009 then change the cost pricing in 2008 to newest cost. Also there are obviously 1186 new item # in the 2009 sheet, is there a way to have them separated if they don't match the 2008 so they can then be added at a later time? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two spreadsheets with pricing information
I have the two spreadsheets open in the same document (Book1) Sheet1 is 2008
Spreadsheet with Column A = Item , Coulmn B = Description, and Column C = Cost. Sheet2 is 2009 and Columns are set up exactly the same. There are 1186 items in Sheet2 that are not in Sheet1 and each row is not exact on each Sheet so when I enter the formula: In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) (Actually have to change the formula to =VLOOKUP(Sheet2!,A1,A:C,False) otherwise formula won't work) and copy down... The spreadsheet then compares the Row in Sheet1 to the exact same Row in Sheet2. It does compare the two and thus gives me the price from Sheet2 in D1 but it isn't looking through the whole Sheet2 to look and see if the same corresponding Item# exists and then giving me the the correct price (Sheet2=2009 Cost) and displaying in D1. If I copy and paste the formula =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)) I receive the following warning popup: Your formulais missing a parenthesis--) or (. Check the formula, and then add the parenthesis in the appropriate place. I really apprecaite you attempt to help however this has not worked, Do you have any other suggestions? Or is there anyone else out there who may have some suggestions. Thank you!!!! "Sheeloo" wrote: Suppose 2008 sheet is named as Sheet1 and 2009 as Sheet2 and Cols A,B,C in both contain item #, descriptions, and costs respectively. In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of Sheet2 =VLOOKUP(A1,Sheet1!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)),"Not found in 2009","Found in 2008") wiil give you more meaningful message... "sta1" wrote: I have two spreadsheets that contain item #, descriptions, and costs. One is from 1008 and the other is for 2009. There are 2460 rows of information in the 2008 and 3586 rows of information. I need to compare the 2 sheets and if the part # exists in 2009 then change the cost pricing in 2008 to newest cost. Also there are obviously 1186 new item # in the 2009 sheet, is there a way to have them separated if they don't match the 2008 so they can then be added at a later time? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two spreadsheets with pricing information
Sorry for not clear... I tried by saying "Suppose 2008 sheet is named as
Sheet1 and 2009 as Sheet2" Change Sheet1 to '2008' and Sheet2 to '2009' everywhere in the formulas I gave (see below); In 2008 enter this in D1 =VLOOKUP(A1,'2009'!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of 2009 =VLOOKUP(A1,'2008'!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,'2008'!A:A,1,False)),"Not found in 2009","Found in 2008") Hope this works... "sta1" wrote: I have the two spreadsheets open in the same document (Book1) Sheet1 is 2008 Spreadsheet with Column A = Item , Coulmn B = Description, and Column C = Cost. Sheet2 is 2009 and Columns are set up exactly the same. There are 1186 items in Sheet2 that are not in Sheet1 and each row is not exact on each Sheet so when I enter the formula: In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) (Actually have to change the formula to =VLOOKUP(Sheet2!,A1,A:C,False) otherwise formula won't work) and copy down... The spreadsheet then compares the Row in Sheet1 to the exact same Row in Sheet2. It does compare the two and thus gives me the price from Sheet2 in D1 but it isn't looking through the whole Sheet2 to look and see if the same corresponding Item# exists and then giving me the the correct price (Sheet2=2009 Cost) and displaying in D1. If I copy and paste the formula =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)) I receive the following warning popup: Your formulais missing a parenthesis--) or (. Check the formula, and then add the parenthesis in the appropriate place. I really apprecaite you attempt to help however this has not worked, Do you have any other suggestions? Or is there anyone else out there who may have some suggestions. Thank you!!!! "Sheeloo" wrote: Suppose 2008 sheet is named as Sheet1 and 2009 as Sheet2 and Cols A,B,C in both contain item #, descriptions, and costs respectively. In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of Sheet2 =VLOOKUP(A1,Sheet1!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)),"Not found in 2009","Found in 2008") wiil give you more meaningful message... "sta1" wrote: I have two spreadsheets that contain item #, descriptions, and costs. One is from 1008 and the other is for 2009. There are 2460 rows of information in the 2008 and 3586 rows of information. I need to compare the 2 sheets and if the part # exists in 2009 then change the cost pricing in 2008 to newest cost. Also there are obviously 1186 new item # in the 2009 sheet, is there a way to have them separated if they don't match the 2008 so they can then be added at a later time? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two spreadsheets with pricing information
My mistake€¦ had an extra comma after '2009'!
Correct formula is =VLOOKUP(A1,'2009'!A:C,3,False) and NOT =VLOOKUP(A1,'2009'!,A:C,3,False) "sta1" wrote: I'm still unable to get any of your suggestions to work. I continue to receive warnings when placing your formula's into the corresponding cell D1. I attempted to send you an e-mail or with a question. Not sure if either one of those is your correct e-mail. If you receive the e-mail and could reply I'd appreciate it. If neither one of those are your e-mail could you please send me an e-mail to so that I can reply back with the question. Thank you for your continued help regarding this issue. "Sheeloo" wrote: Sorry for not clear... I tried by saying "Suppose 2008 sheet is named as Sheet1 and 2009 as Sheet2" Change Sheet1 to '2008' and Sheet2 to '2009' everywhere in the formulas I gave (see below); In 2008 enter this in D1 =VLOOKUP(A1,'2009'!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of 2009 =VLOOKUP(A1,'2008'!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,'2008'!A:A,1,False)),"Not found in 2009","Found in 2008") Hope this works... "sta1" wrote: I have the two spreadsheets open in the same document (Book1) Sheet1 is 2008 Spreadsheet with Column A = Item , Coulmn B = Description, and Column C = Cost. Sheet2 is 2009 and Columns are set up exactly the same. There are 1186 items in Sheet2 that are not in Sheet1 and each row is not exact on each Sheet so when I enter the formula: In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) (Actually have to change the formula to =VLOOKUP(Sheet2!,A1,A:C,False) otherwise formula won't work) and copy down... The spreadsheet then compares the Row in Sheet1 to the exact same Row in Sheet2. It does compare the two and thus gives me the price from Sheet2 in D1 but it isn't looking through the whole Sheet2 to look and see if the same corresponding Item# exists and then giving me the the correct price (Sheet2=2009 Cost) and displaying in D1. If I copy and paste the formula =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)) I receive the following warning popup: Your formulais missing a parenthesis--) or (. Check the formula, and then add the parenthesis in the appropriate place. I really apprecaite you attempt to help however this has not worked, Do you have any other suggestions? Or is there anyone else out there who may have some suggestions. Thank you!!!! "Sheeloo" wrote: Suppose 2008 sheet is named as Sheet1 and 2009 as Sheet2 and Cols A,B,C in both contain item #, descriptions, and costs respectively. In sheet1 enter this in D1 =VLOOKUP(A1,Sheet2!,A:C,3,False) and copy down... It will give you the updated price if the item is there in 2009 sheet or #N/A if it is not found... If you enter this in D1 of Sheet2 =VLOOKUP(A1,Sheet1!A:A,1,False) It will give you #N/A if it is not present in 2008 sheet =IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,False)),"Not found in 2009","Found in 2008") wiil give you more meaningful message... "sta1" wrote: I have two spreadsheets that contain item #, descriptions, and costs. One is from 1008 and the other is for 2009. There are 2460 rows of information in the 2008 and 3586 rows of information. I need to compare the 2 sheets and if the part # exists in 2009 then change the cost pricing in 2008 to newest cost. Also there are obviously 1186 new item # in the 2009 sheet, is there a way to have them separated if they don't match the 2008 so they can then be added at a later time? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing spreadsheets | Excel Worksheet Functions | |||
| Comparing two spreadsheets | Excel Worksheet Functions | |||
| Comparing Two Spreadsheets | Excel Discussion (Misc queries) | |||
| Comparing Two Spreadsheets | Excel Worksheet Functions | |||
| Comparing Spreadsheets | Excel Worksheet Functions | |||
Linear Mode
