+ 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

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


    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
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365

    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?
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    That might help a lot, i'll give it a go on monday


+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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