Data Summary

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimmy Joseph - 12 May 2006 09:03 GMT

Hello All,

I have the following data table in excel.

Qty    Product    Part1    Part2    Part3    Part4
10    a1         a    x    e    f
10    a2         a    y    e    g
10    a3         b    x    e    h
20    a4         m    p    e    u
50    a5         n    q    e    i
60    x2         a    x    e    t

The Qty mentioned is common for product,part1,part2,part3,part4 in a row.

Now i would like to have an automated summary in a different sheet as
below

> Part1    Qty    Part2    Qty    Part3    Qty    Part4    Qty
> a         80    x    80    e    160    f    10
> b         10    y    10            g    10
> m         20    p    20            h    10
> n         50    q    50            u    20
>                              i    50
>                              t    60

How do I do it?

Regards,

Jimmy Jospeh

Reply to this Message

Ardus Petus - 12 May 2006 09:36 GMT

In summary sheet, you can get the totals with a formula like:
=SUMIF(Products!$C$2:$C$7,A2,Products!$A$2:$A$7)
See example: http://cjoint.com/?fmkJhzVi8q

To automate the production of parts lists in Summary, you can use
Data>Filter>Advanced filter to take out dupes.

HTH
--
AP

> Hello All,
>
[quoted text clipped - 26 lines]
>
> Jimmy Jospeh

Reply to this Message

Jimmy Joseph - 13 May 2006 06:25 GMT

Can we do the summary by defining a macro?

The difficulty with the suggested solution :
1.I am not able to advance filter the unique data to a separate
sheet.
2.Every day the data gets changed, so have to repeat the exercise
daily and is very time consuming.

Regards,

Jimmy Joseph

Subject: Re: Data Summary
From: "Ardus Petus" <ardus.petus@laposte.net>
Newsgroups: microsoft.public.excel

In summary sheet, you can get the totals with a formula like:
=SUMIF(Products!$C$2:$C$7,A2,Products!$A$2:$A$7)
See example: http://cjoint.com/?fmkJhzVi8q

To automate the production of parts lists in Summary, you can use
Data>Filter>Advanced filter to take out dupes.

HTH
--
AP

Reply to this Message