How can I combine data from two sheets where field contents match?

[amaries]

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

 

[Pete_UK]

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)),"",VLOOKUP(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

 

[bj]

checkout vlookup in help
in the cells you want to pull data to
=if(isna(vlookup(Part_number,Sheet2!$A$G,appropriate_column_number,0)),"",vlookup(Part_number,Sheet2!$A$G,appropriate_column_number,0)))

 

[amaries]

Thank you for responding so quick! I am sooo close. Here is my formula,
=IF(ISNA(VLOOKUP(A291,Sheet2!A$2$27637,3,0)),"none",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.

 

[Pete_UK]

The second part of your formula does not quite match the first part.
Try this:

=IF(ISNA(VLOOKUP(A291,Sheet2!A$2$27637,2,0)),"none",
VLOOKUP(A291,Sheet2!A$2$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,COLUMN(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

 

[amaries]

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?

 

[Pete_UK]

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