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

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

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