Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching criteria and summing
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. -- Arturo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching criteria and summing
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 --- "Arturo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Criteria Summing | Excel Worksheet Functions | |||
matching and summing across sheets | Excel Discussion (Misc queries) | |||
Summing certain criteria - Advanced | Excel Discussion (Misc queries) | |||
Need Help: Summing Multiple Criteria | Excel Worksheet Functions | |||
Summing based on 2 criteria | Excel Discussion (Misc queries) |