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 |
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. |
All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com