+ Reply to Thread
Results 1 to 3 of 3

Normalizing historical data: Will pivot table work for this?

  1. 09-27-2012, 01:38 AM #1
    portsample
    portsample is offline
    Registered User
    Join Date
    12-31-2008
    Location
    Alaska, (not Anchorage)
    MS-Off Ver
    Excel 2010, 2016, and 365
    Posts
    78

    Normalizing historical data: Will pivot table work for this?

    I have several historic data sets that I would like to normalize to a common structure.

    I would like to convert this:
    date |area | color | Dodge | BMW | Delorean | Ford
    12/2/2012 | London | blue | 2 | 3 | 1 | 6
    12/3/2012 | Tokyo | green | 1 | 5 | 2 | 1
    12/3/2012 | Juneau | white | 0 | 1 | 1 | 9
    to this:
    date | area | color | quantity | make
    12/2/2012 | London | blue | 2 | Dodge
    12/2/2012 | London | blue | 3 | BMW
    12/2/2012 | London | blue | 1 | Delorean
    12/2/2012 | London | blue | 6 | Ford
    12/3/2012 | Tokyo | green | 1 | Dodge
    12/3/2012 | Tokyo | green | 5 | BMW
    12/3/2012 | Tokyo | green | 2 | Delorean
    12/3/2012 | Tokyo | green | 1 | Ford
    12/3/2012 | Juneau | white | 0 | Dodge
    12/3/2012 | Juneau | white | 1 | BMW
    12/3/2012 | Juneau | white | 1 | Delorean
    12/3/2012 | Juneau | white | 9 | Ford

    Is this something that pivot table can handle?
    I confess to being a bit lost regarding how to frame this problem...even though it seems so simple!
    TIA for ideas and suggestions.
    Reply With QuoteRegister To Reply
  2. 09-27-2012, 10:42 AM #2
    portsample
    portsample is offline
    Registered User
    Join Date
    12-31-2008
    Location
    Alaska, (not Anchorage)
    MS-Off Ver
    Excel 2010, 2016, and 365
    Posts
    78

    Re: Normalizing historical data: Will pivot table work for this?

    Quote Originally Posted by portsample View Post
    I have several historic data sets that I would like to normalize to a common structure.

    I would like to convert this:
    date |area | color | Dodge | BMW | Delorean | Ford
    12/2/2012 | London | blue | 2 | 3 | 1 | 6
    12/3/2012 | Tokyo | green | 1 | 5 | 2 | 1
    12/3/2012 | Juneau | white | 0 | 1 | 1 | 9
    to this:
    date | area | color | quantity | make
    12/2/2012 | London | blue | 2 | Dodge
    12/2/2012 | London | blue | 3 | BMW
    12/2/2012 | London | blue | 1 | Delorean
    12/2/2012 | London | blue | 6 | Ford
    12/3/2012 | Tokyo | green | 1 | Dodge
    12/3/2012 | Tokyo | green | 5 | BMW
    12/3/2012 | Tokyo | green | 2 | Delorean
    12/3/2012 | Tokyo | green | 1 | Ford
    12/3/2012 | Juneau | white | 0 | Dodge
    12/3/2012 | Juneau | white | 1 | BMW
    12/3/2012 | Juneau | white | 1 | Delorean
    12/3/2012 | Juneau | white | 9 | Ford

    Is this something that pivot table can handle?
    I confess to being a bit lost regarding how to frame this problem...even though it seems so simple!
    TIA for ideas and suggestions.
    <....bump>
    Reply With QuoteRegister To Reply
  3. 10-08-2012, 03:47 PM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Normalizing historical data: Will pivot table work for this?

    re asked at
    http://www.excelforum.com/excel-char...ble-parse.html
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1