+ Reply to Thread
Results 1 to 2 of 2

Summarizing data

  1. #1
    Pedro AM
    Guest

    Summarizing data

    Hi all

    I need to summarze data in this way:
    I have a database with items and in Column A and their Category in Column B
    plus dta in C
    E.G
    Item category Data
    a X 5
    b Z 1
    c X 3
    d X 6
    I want it to look at the item and pick up the data in C and reclassify
    according to another table where
    Item category Data
    a X 5
    b X 1
    c X 3
    d Z 6
    But summarised so I have totals for X and Z which would be 9 and 6 as
    opposed to 14 and 1.
    Needles to say that I have many more categories and much more data for this.

    Does anyone know a way to do this bearing in mind that the data table is in
    a different file to the table that contains the output and the equivalencies
    (e.g d was X and now Z).

    Any help would be appreciated. Thank you


  2. #2
    Toppers
    Guest

    RE: Summarizing data

    Here is "model solution" you could adapt. I couldn't find a way to combine
    the conversion of categories and the summation of a category into a single
    formula. This doesn't mean it cannot be done!

    Orginal table in A1 to C5

    Item Category Data
    a X 5
    b Z 1
    c X 3
    d X 6

    Conversion in G1 to I5
    Item Old Cat New Cat
    a X X
    b Z X
    c X X
    d X Z

    New table in G9 to G13

    Item Category Data
    a X 5
    b X 1
    c X 3
    d Z 6

    in H10: =INDEX($I$2:$I$5,MATCH(1,(A2=$G$2:$G$5)*(B2=$H$2:$H$5),0))

    Enter with Ctrl+shift + enter (an array formula) and copy down
    in I10: = C2 and copy down


    Total in g16 to H18

    Category Total
    X 9
    Z 6


    in H17: =SUMIF($H$10:$H$13,G17,$I$10:$I$13) and copy down

    HTH

    "Pedro AM" wrote:

    > Hi all
    >
    > I need to summarze data in this way:
    > I have a database with items and in Column A and their Category in Column B
    > plus dta in C
    > E.G
    > Item category Data
    > a X 5
    > b Z 1
    > c X 3
    > d X 6
    > I want it to look at the item and pick up the data in C and reclassify
    > according to another table where
    > Item category Data
    > a X 5
    > b X 1
    > c X 3
    > d Z 6
    > But summarised so I have totals for X and Z which would be 9 and 6 as
    > opposed to 14 and 1.
    > Needles to say that I have many more categories and much more data for this.
    >
    > Does anyone know a way to do this bearing in mind that the data table is in
    > a different file to the table that contains the output and the equivalencies
    > (e.g d was X and now Z).
    >
    > Any help would be appreciated. Thank you
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1