+ Reply to Thread
Results 1 to 3 of 3

How can I port data into new table, using any means necessary

  1. #1
    Registered User
    Join Date
    02-20-2007
    Posts
    12

    How can I port data into new table, using any means necessary

    Hi

    I have a very unusual table that i want to upgrade first to a more normal style. I've attached a dummy but it also explain it below

    explanation of table: product 000001 is named AB and contains multiple colors in different amount. and so on

    code product color amount
    000001 AB orange 20
    000001 AB blue 40
    000001 AB black 30
    000001 AB red 15
    000001 AB purple 70
    000001 AB white 20
    000001 AB yellow 10
    000002 CD red 70
    000002 CD white 1000
    000002 CD black 100
    000002 CD green 500
    000002 EF orange 30
    000003 EF red 30
    000003 EF brown 10
    000003 EF black 10

    I want to make it like this
    black blue brown, etc
    code product
    000001 AB 20 40
    000002 CD 100
    000003 EF 10



    The actual table has >10000 rows and would result in a new table of 2500 by 600 columns. (not bothered excels limits on column for now) so typing the whole is not what i want to do.


    I was thinking to use the labels/headers in the new table as parameters to look up a value. So by using 000001 and black in a macro/formula it would give me 20 and place that in the correct cell.

    Is this something that can be done? Vlookup seems to be shorthanded for this.

    hopefully someone been there before,

    Science Boy


    PS in the end I intend to bring the new table into msAccess so if you know a good way to put the original in access properly i'm also interested
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    How can I port data into new table, using any means necessary

    Have you considered using a Pivot Table to do the heavy lifting?

    Try this (using your sample file)

    Add a heading to the Color column (C9: Color)

    Then...from the Excel Main Menu
    <data><pivot table>.....................Click [Next]
    Set the data range ($A$9:$D$24).....Click [Next]
    Click the [Layout] button
    ROW: Drag the Code and ProductName fields here
    (Double-click on each field and set Subtotals to None)

    COLUMN: Drag the Color field here
    (Double-click on the field and set Subtotals to None)

    DATA: Drag the Amount field here
    If it doesn't display as Sum of Amount...
    Double-click on it and set the formula to SUM

    Click [OK]
    Select a destination cell to build the Pivot Table (eg Z15)
    Click [Finish]

    Please Login or Register  to view this content.
    Note: I set the Grand Totals for the table to None
    I also edited the last 000002 product name to "CD"

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    02-20-2007
    Posts
    12
    That might help a lot, i'll give it a go on monday


    thnx

+ 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.05483 seconds
  • Memory Usage 8,889KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (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)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (3)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (3)postbit_legacy
  • (3)postbit_onlinestatus
  • (3)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 (39):
  • ./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 

Hooks Called (47):
  • 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
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_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