Matching criteria and summing

  • Thread starter Thread starter Arturo
  • Start date Start date
A

Arturo

I have 2 worksheets, same workbook, with similar information and wish on a
3rd worksheet to add the like items together and also display the unlike
items.
EXAMPLE:
SHEET 1 SHEET 2
A B C A B C
1 ITEM QTY AMT ITEM QTY AMT
2 2x4x8 8 $24.00 4X4X8 2 $16.00
3 4x4x8 10 $80.00 2x6x8 10 $50.00
4 2X10X8 2 $24.00

Note that not all products are listed on each sheet and the order will
change monthly. Thank you.
 
Try the power and ease of a pivot table ..
In the 3rd sheet,
1. Copy n paste special as values the table from the 1st sheets (with the
col headers). Repeat the copy n paste special as values for the data only
from the 2nd sheet, paste it below the earlier. 15 seconds manual effort. The
order of pasting
doesn't matter but there should be only 1 row of col headers.
2. Create a pivot on the merged table. In Layout, place "Item" in ROW, "Qty"
and "Amt" in DATA (it'll show as Sum of ..). Click to finish. In the pivot
sheet, drag "Data" and drop it over "Total" to re-arrange into columns.
That's it. The end results will be exactly what you seek. Another 15 seconds
effort. You're done within 30 seconds or so. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top