Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
Hi All
I hope that someone can help with my problem. On "Sheet1" I have two columns, A = Code, B =Qty. What happens is that i extract a large amount of data from a database to my excel worksheet. These columns spoken about above, hold my information. What i need is a summary of this information, let me explain with an example. Let us say i'm looking @ product "a", wich has the code "a". When i pull information out of the database there might be 20 rows of product "a". I need a macro that adds all the quantities (column B) for all instances of product "a", and puts the sum of those quantities into "Sheet2" Cell "B1", with the code next to it on Cell "A1". The macro then needs to look at the next item and place the results under the previous ones on "sheet2". I hope i have explained the problem in an undersandable way! Thanks in advance Ant |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
Ant,
Select your data table, and use Data | Pivot Table. Drag the code to the row field, and the Qty to the data field, and set the Qty to return a sum, and you're done. Look in help for more on Pivot Tables. HTH, Bernie MS Excel MVP "Ant Nutting" wrote in message ... Hi All I hope that someone can help with my problem. On "Sheet1" I have two columns, A = Code, B =Qty. What happens is that i extract a large amount of data from a database to my excel worksheet. These columns spoken about above, hold my information. What i need is a summary of this information, let me explain with an example. Let us say i'm looking @ product "a", wich has the code "a". When i pull information out of the database there might be 20 rows of product "a". I need a macro that adds all the quantities (column B) for all instances of product "a", and puts the sum of those quantities into "Sheet2" Cell "B1", with the code next to it on Cell "A1". The macro then needs to look at the next item and place the results under the previous ones on "sheet2". I hope i have explained the problem in an undersandable way! Thanks in advance Ant |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Needed Please
Put the code in A1
In B1 =SUMIF(Sheet1!A:A,A1,Sheet1!B:B) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ant Nutting" wrote in message ... Hi All I hope that someone can help with my problem. On "Sheet1" I have two columns, A = Code, B =Qty. What happens is that i extract a large amount of data from a database to my excel worksheet. These columns spoken about above, hold my information. What i need is a summary of this information, let me explain with an example. Let us say i'm looking @ product "a", wich has the code "a". When i pull information out of the database there might be 20 rows of product "a". I need a macro that adds all the quantities (column B) for all instances of product "a", and puts the sum of those quantities into "Sheet2" Cell "B1", with the code next to it on Cell "A1". The macro then needs to look at the next item and place the results under the previous ones on "sheet2". I hope i have explained the problem in an undersandable way! Thanks in advance Ant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro Help needed | Excel Discussion (Misc queries) | |||
Macro needed | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |