Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I have two sheets in same workbook, first one is a list of parts with lots of
other data (product code, qty, etc) In the second sheet I have a list of parts with other data (#times quoted, qty, etc) I need to get the data from sheet two to appear on sheet one where the part numer is the same. So my first sheet will pull in the additional data on the parts from the second sheet. So like a link but where the part number matches. Sheet 1 a)Part# b)ProductCode c)Qty on sales orders d)Avg Sell Price e)Qty on work orders f)Avg Cost g) (from sheet 2) Qty on quotes h) (from sheet 2) Avg. Quote Price I have use paste special but I couldn't figure out how to only merge where a field content matched. Any tips to accomplish this (under time constraint, CEO wants by EODay). Thanks for any help |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Use VLOOKUP. In G2 of the main sheet enter this formula:
=VLOOKUP(A2,Sheet2!A$2:F$1000,3,0) this assumes that you have 1000 records in Sheet2 (doesn't matter if you have less) and that the data is in columns A to F - adjust as necessary. The Part# has to be in the left-most column of the table (I've assumed column A), and I've assumed that the value you want to be returned is in the third column of this table (i.e. column C in Sheet2). The way the formula works is to try to find an exact match between A2 of Sheet1 in A2:A1000 of Sheet2. If there is a match, then the formula returns the value from the 3rd column of the table in Sheet2. If there is no match then the formula will return #N/A - to avoid this use this variation: =IF(ISNA(VLOOKUP(A2,Sheet2!A$2:F$1000,3,0)),"",VLO OKUP(A2,Sheet2!A$2:F $1000,3,0)) this will give you a blank cell if there is no match (or you can change the "" to some message , like "none"). Copy the formula down for as many items as you have in Sheet1. You can use a similar formula to get other data from Sheet2 - all you need to do is change the third parameter of the VLOOKUP from a 3 to the column number that you want to return the data from. Hope this helps. Pete On Jul 6, 8:30 pm, amaries wrote: I have two sheets in same workbook, first one is a list of parts with lots of other data (product code, qty, etc) In the second sheet I have a list of parts with other data (#times quoted, qty, etc) I need to get the data from sheet two to appear on sheet one where the part numer is the same. So my first sheet will pull in the additional data on the parts from the second sheet. So like a link but where the part number matches. Sheet 1 a)Part# b)ProductCode c)Qty on sales orders d)Avg Sell Price e)Qty on work orders f)Avg Cost g) (from sheet 2) Qty on quotes h) (from sheet 2) Avg. Quote Price I have use paste special but I couldn't figure out how to only merge where a field content matched. Any tips to accomplish this (under time constraint, CEO wants by EODay). Thanks for any help |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
checkout vlookup in help
in the cells you want to pull data to =if(isna(vlookup(Part_number,Sheet2!$A$G,appropria te_column_number,0)),"",vlookup(Part_number,Sheet2 !$A$G,appropriate_column_number,0))) "amaries" wrote: I have two sheets in same workbook, first one is a list of parts with lots of other data (product code, qty, etc) In the second sheet I have a list of parts with other data (#times quoted, qty, etc) I need to get the data from sheet two to appear on sheet one where the part numer is the same. So my first sheet will pull in the additional data on the parts from the second sheet. So like a link but where the part number matches. Sheet 1 a)Part# b)ProductCode c)Qty on sales orders d)Avg Sell Price e)Qty on work orders f)Avg Cost g) (from sheet 2) Qty on quotes h) (from sheet 2) Avg. Quote Price I have use paste special but I couldn't figure out how to only merge where a field content matched. Any tips to accomplish this (under time constraint, CEO wants by EODay). Thanks for any help |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Thank you for responding so quick! I am sooo close. Here is my formula,
=IF(ISNA(VLOOKUP(A291,Sheet2!A$2:P$27637,3,0)),"no ne",VLOOKUP(A291,Sheet2!A$2:A27926,3,)) I am getting 'none' where there is no match, but #REF on the ones where they actually do match? My Sheet1 has the PARTID in A which is to match with the PARTID in A on sheet2. Sheet1 has data through column P. Starting in Column Q (where I put this formula) I want Q(Sheet2/columnB) R(Sheet2/columnC) S(Sheet2/columnD) T(Sheet2/columnE) Note: Sheet1 has 27926 rows, Sheet2 has only 17402 Where am I going wrong that is is displaying #REF? 'none' is working. |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
The second part of your formula does not quite match the first part.
Try this: =IF(ISNA(VLOOKUP(A291,Sheet2!A$2:P$27637,2,0)),"no ne", VLOOKUP(A291,Sheet2!A$2:P$27637,2,0)) if you want data from the second column of Sheet2 - actually, your range for P only needs to go to 17402, but it doesn't matter if it is too big. As you want to get data in consecutive columns from Sheet2, can I suggest this alternative: =IF(ISNA(VLOOKUP($A291,Sheet2!$A$2:$P$27637,COLUMN (B2), 0)),"none",VLOOKUP($A291,Sheet2!$A$2:$P$27637,COLU MN(B2),0)) The function COLUMN(B2) will return 2 (which is what you want with the formula in column Q of Sheet1. When you copy this formula into the next 3 columns, this will become COLUMN(C2), COLUMN(D2), COLUMN(E2) etc, which in turn will give 3, 4, and 5, i.e. the columns where you want to get the data from. Notice that I have put $ symbols in front of some of the column letters in the cell references - these will not change when you copy the formula across. So, all you need to do is put this formula in Q291 and copy it into R291:T291, format those cells appropriately and then copy the formulae down the column - a quick way to do this is to double-click the fill handle with the cursor in Q291 (the small black square in the bottom right corner of the cursor). Incidentally, the above is all one formula - be wary of spurious line breaks on the newsgroups (often introducing a - character at the line break). Hope this helps. Pete On Jul 6, 10:12 pm, amaries wrote: Thank you for responding so quick! I am sooo close. Here is my formula, =IF(ISNA(VLOOKUP(A291,Sheet2!A$2:P$27637,3,0)),"no ne",VLOOKUP(A291,Sheet2!A*$2:A27926,3,)) I am getting 'none' where there is no match, but #REF on the ones where they actually do match? My Sheet1 has the PARTID in A which is to match with the PARTID in A on sheet2. Sheet1 has data through column P. Starting in Column Q (where I put this formula) I want Q(Sheet2/columnB) R(Sheet2/columnC) S(Sheet2/columnD) T(Sheet2/columnE) Note: Sheet1 has 27926 rows, Sheet2 has only 17402 Where am I going wrong that is is displaying #REF? 'none' is working. |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Additional note may be causing the problem?
In my Sheet1 there happens to be more than one row with the same PARTID, however in Sheet2 the PARTID is only listed once. In my sheet1 they are listed more than once because I could not get the rows combined. In other words, (Sheet1) A B C D E F PARTID Description COrderCount CO Qty AvgPrice WOrderCount ROW1(abc) (collet) (1) (1) (750.00) (blank) ROW2(abc) (collet) (blank) (blank) (blank) (1) Sheet2 A B C D E PARTID QuoteCount PartQty AvgUnitPrice AvgTotalPrice ROW1(abc) (1) (1) (750.00) (750.00) ROW2(def) (1) (6) (1.60) ( 9.60) Am I going to have to figure out how to get the Sheet1 down to one row per partid for this to work? |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Yes, the partID is a unique code which identifies the item - as such,
it is essential to ensure that you do not have duplicates in Table 1. Work through my previous response and when you have got that working you will see what happens - you will get duplicates in columns Q to T. However, we can address that afterwards with advanced filter. Let me know when the VLOOKUPs are working, as then you can fix the values and we won't need Table2 anymore. Pete On Jul 6, 10:38 pm, amaries wrote: Additional note may be causing the problem? In my Sheet1 there happens to be more than one row with the same PARTID, however in Sheet2 the PARTID is only listed once. In my sheet1 they are listed more than once because I could not get the rows combined. In other words, (Sheet1) A B C D E F PARTID Description COrderCount CO Qty AvgPrice WOrderCount ROW1(abc) (collet) (1) (1) (750.00) (blank) ROW2(abc) (collet) (blank) (blank) (blank) (1) Sheet2 A B C D E PARTID QuoteCount PartQty AvgUnitPrice AvgTotalPrice ROW1(abc) (1) (1) (750.00) (750.00) ROW2(def) (1) (6) (1.60) ( 9.60) Am I going to have to figure out how to get the Sheet1 down to one row per partid for this to work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel: Combine and match two data | Excel Discussion (Misc queries) | |||
How do I combine data from several sheets into one main sheet? | Excel Worksheet Functions | |||
combine data from multiple sheets | Excel Discussion (Misc queries) | |||
How do I combine and match data from 2 sheets | Excel Discussion (Misc queries) | |||
How to append/combine (not add) data in several sheets into one? | Excel Discussion (Misc queries) |