+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05642 seconds
  • Memory Usage 8,848KB
  • Queries Executed 16 (?)
More Information
Template Usage (30):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (2)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_legacy
  • (2)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./includes/functions_prefix.php 

Hooks Called (44):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1