Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Consolidation formula
Hi,
I am trying to workout how to consolidate a list on a separate sheet. ie. Sheet1 A B C 1 Product Category Sales 2 Cat Pets 10 3 Bird Pets 23 4 Bell Misc 44 5 Collar Misc 36 6 Dog Pets 61 7 Ball Toy 33 8 Mirror Toy 64 9 Mouse Pets 24 10 Cat Pets 73 11 Cat Pets 11 12 Dog Pets 34 Sheet2 A 1 Pets 2 3 Cat 4 Bird 5 Dog 6 Mouse where the formula in Sheet2.A3:A6 looks at the value in Sheet2.A1 and returns a consolidated list of the values in cell Sheet1.A2:A12. I know the easiest way to do this is with a Pivot Table but...Any help appreciated. Cheers Reuben |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Consolidation formula
Hi Reuben
You're quite right, you should do it with a Pivot Table - why not? If you do want to do it the hard way<bg, then in cell B3 of Sheet2 =SUMPRODUCT((Sheet1$B$1:$B$100=$A$1)*(Sheet1!$A$1: $A$100=$A3)*Sheet1!$C$1:$C$100) Copy down as required -- Regards Roger Govier "reuben" wrote in message ... Hi, I am trying to workout how to consolidate a list on a separate sheet. ie. Sheet1 A B C 1 Product Category Sales 2 Cat Pets 10 3 Bird Pets 23 4 Bell Misc 44 5 Collar Misc 36 6 Dog Pets 61 7 Ball Toy 33 8 Mirror Toy 64 9 Mouse Pets 24 10 Cat Pets 73 11 Cat Pets 11 12 Dog Pets 34 Sheet2 A 1 Pets 2 3 Cat 4 Bird 5 Dog 6 Mouse where the formula in Sheet2.A3:A6 looks at the value in Sheet2.A1 and returns a consolidated list of the values in cell Sheet1.A2:A12. I know the easiest way to do this is with a Pivot Table but...Any help appreciated. Cheers Reuben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Consolidation | Excel Discussion (Misc queries) | |||
data consolidation | Excel Discussion (Misc queries) | |||
Need how-to or example for data consolidation | Excel Worksheet Functions | |||
Data Consolidation Help Needed. | Excel Discussion (Misc queries) | |||
data consolidation | Excel Discussion (Misc queries) |