Combining worksheets with common column but different number of ro

[Guest]

I have two excel spreadsheets, one that is complete with product part
numbers, item descriptions, prices, quantity, photo url's, etc. I get a
daily update in the form of a spreadhseet with part number and quantity only.

Is there a way that excel can recognize that column A in each worksheet are
part numbers, and if the part number in column A of each worksheet matches
that excel should update column G with the new quantity (column B from
worksheet 2)?

A simple cut and paste will not work as there are different numbers of rows
in each worksheet as part numbers become discontinued. Any help with this
would be appreciated. Thank you.

 

[Harlan Grove]

I have two excel spreadsheets, one that is complete with product
part numbers, item descriptions, prices, quantity, photo url's, etc.
I get a daily update in the form of a spreadhseet with part number
and quantity only.

Is there a way that excel can recognize that column A in each
worksheet are part numbers, and if the part number in column A of
each worksheet matches that excel should update column G with the
new quantity (column B from worksheet 2)?

....

I'll assume these are separate files, which in Excel terminology are
workbooks. I'll also assume both are open. Further, I'll assume both
have their tables starting in cell A1 with headings in row 1, part
number in column A in both and quantity in column D of the first
workbook and column B of the second.

Would you want the existing values in worksheet 1 retained if there
were no corresponding part number in workbook 2? Or would you want to
show 0 or "" or "n/a"? Either way, use an additional column in the
first workbook to pull in data from the second workbook using a
VLOOKUP formula.

[FirstWorkbook.xls]FirstWorksheet!X2:
=IF(COUNT(MATCH(A2,[SecondWorkbook.xls]SecondWorksheet!$A:$A,0)),
VLOOKUP(A2,[SecondWorkbook.xls]SecondWorksheet!$A:$B,2,0),D2)

Fill X2 down as far as needed, then select X2:X#, copy, move to D2,
paste special as Values, then clear X2:X#.

The D2 as the third argument to IF in the formula above pulls in the
existing quantity value if there's no entry in the second workbook. If
you want to show 0, replace D2 with 0 before filling the formula down.
Similarly for any other value you'd want to use in place of the
existing quantity value.

 

[Guest]

Harlan-

Thank you for taking the time to reply to this post. WOW this is confusing.
And I thought was well versed in Excel!

First, I am assuming (and I hate to do so) that I should replace
"FirstWorkbook.xls" to the actual title of the workbook (Inventory.xls) and
"FirstWorksheet!X2" to the actual worksheet title (Inventory!X2). Secondly,
when you say to fill X2 down as far as needed, what do you mean by that? I
tried this as you suggested an I am clearly having trouble with understanding
your directions. I am sure this is on my end but any further explanation
would be greatly appreciated. Thanks!

I have two excel spreadsheets, one that is complete with product
part numbers, item descriptions, prices, quantity, photo url's, etc.
I get a daily update in the form of a spreadhseet with part number
and quantity only.

Is there a way that excel can recognize that column A in each
worksheet are part numbers, and if the part number in column A of
each worksheet matches that excel should update column G with the
new quantity (column B from worksheet 2)?

....

I'll assume these are separate files, which in Excel terminology are
workbooks. I'll also assume both are open. Further, I'll assume both
have their tables starting in cell A1 with headings in row 1, part
number in column A in both and quantity in column D of the first
workbook and column B of the second.

Would you want the existing values in worksheet 1 retained if there
were no corresponding part number in workbook 2? Or would you want to
show 0 or "" or "n/a"? Either way, use an additional column in the
first workbook to pull in data from the second workbook using a
VLOOKUP formula.

[FirstWorkbook.xls]FirstWorksheet!X2:
=IF(COUNT(MATCH(A2,[SecondWorkbook.xls]SecondWorksheet!$A:$A,0)),
VLOOKUP(A2,[SecondWorkbook.xls]SecondWorksheet!$A:$B,2,0),D2)

Fill X2 down as far as needed, then select X2:X#, copy, move to D2,
paste special as Values, then clear X2:X#.

The D2 as the third argument to IF in the formula above pulls in the
existing quantity value if there's no entry in the second workbook. If
you want to show 0, replace D2 with 0 before filling the formula down.
Similarly for any other value you'd want to use in place of the
existing quantity value.

 

[Harlan Grove]

First, I am assuming (and I hate to do so) that I should replace
"FirstWorkbook.xls" to the actual title of the workbook (Inventory.xls)
and "FirstWorksheet!X2" to the actual worksheet title (Inventory!X2).
Correct.

Secondly, when you say to fill X2 down as far as needed, what do you mean
by that? . . .

If your first table spanned, say, A1:W200 with headings in row 1, and if you
entered the formula I gave in X2, you could drag the little square at the
bottom right corner of the active cell border around X2 downwards, which
would expand the selected range as you did so. If you dragged it down so
that the selected range became X2:X200 then released your mouse button,
Excel would fill the formula in X2 into the cells in X3:X200. It's the same
effect as copying X2, selecting X3:X200 and pressing [Enter], which would
paste into X3:X200 and end Cut/Copy mode.