Excel Merging Two Reports - Similar Fields, Different Values Question

[Scottie318]

I am currently working on a project as part of an internship with m
employer. The director or marketing wants me to combine two exce
files, one being a cost sheet, the other being a revenue sheet. Th
cost sheet and the revenue sheet both have like fields but differen
values. Here is a very simple example:

Sheet One:

Two Columns
Item Value
Stroller 4
Baby 1

Sheet Two:

Two Columns
Item Value
Stroller 7
Baby 9


I need to make the end result look similar to this:

Three Columns
Item Value (from cost) Value (from revenue)
Baby 1 7
Stroller 4 9

I would just do this manually but each sheet is roughly 2000 rows. I
there anyway to easily combine the sheets together and put the tw
values into two separate columns?

Any help is greatly appreciated

 

[jeff]

Hi,

YOu could do it with a VBA macro, by why not just
copy the 2nd sheet's value column and past into
sheet 1? Simple. Fast. YOu're done.

jeff

 

[jeff]

Hi,

Whoops!! Obviously my earlier suggestion won't work
if the items don't match: ie, Sheet1!A1 = Baby, but
Sheet2!A1 = Stroller. (Stupid me.)

A Vlookup or Find function would work, I think.

jeff

 

[Dave Peterson]

A couple of ways:

Merge your data into one giant worksheet, but you're going to use 3 columns.

Item Cost Revenue

(insert a new column between A & B for the revenue worksheet, then copy|paste to
the new sheet.

Only keep one header row.

The select A1:C4000 (as many rows as you have)
data|pivottable.
follow the wizard until you hit the dialog with a "Layout" button on it.
click that Layout button.

Drag item to the row field
Drag Cost to the Data field--but double click it and change it to "Sum of"
Same with Revenue

Finish up the wizard.

Click on the thing that looks like a button (named Data).
drag it to the right one cell.

Tada!

This works as long as there's a maximum of one value per cost and one value per
revenue.

=============
Another way.
Create a new worksheet but with just the Item names in column A.
Copy column A from one worksheet
paste to column A of new worksheet

copy column A from the 2nd worksheet
paste under the last value in column A of the new worksheet
(only keep one header row)

Select column A.
Data|filter|advanced filter
Use the techniques at Debra Dalgleish to copy the unique values to B1 of the
same worksheet.
http://www.contextures.com/xladvfilter01.html#FilterUR

Delete column A
Sort column A (if you want) (formerly column B)

Put this in B2 and drag down:

=if(iserror(vlookup(A2,sheet1!$a:$b,2,0)),"missing",
vlookup(a2,sheet1!$a:$b,2,0))

Then put this in C2 and drag down:
=IF(ISERROR(VLOOKUP(A2,Sheet2!$A:$B,2,0)),"missing",
VLOOKUP(A2,Sheet2!$A:$B,2,0))

Adjust the names of the sheets to match (sheet1/sheet2).

tada, two!