+ Reply to Thread
Results 1 to 2 of 2

Convert rows with multiple unique columns into individual rows for pivot table management

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2013
    Posts
    1

    Convert rows with multiple unique columns into individual rows for pivot table management

    Hi there - I just joined this forum today, and am thrilled with some of the tips I've learned already - thank you!
    My question is as follows:

    I have a spreadsheet with 750K rows in which the external survey company's feedback data originally looked like this (I couldn't figure out how to make it look more like a table without doing a lot of manual work, but hopefully you get the picture):

    Product Name Address Company Survey Date Rating Survey Feedback1 Survey Feedback2 SurveyFeedback3 Survey Feedback4
    Product A Bill Smith Seattle, WA Happy Foods Corporation 10/15/2012 5 Product has misleading claims Not Relevant to me Offensive Product Imaging Other - Product A sucks!!! I'd never buy it
    Product A John Jacobs Bellevue, WA Better Products Marketing 10/15/2012 3 Not Relevant to me Other - I don't use this stuff.
    Product B Dave Barrett Spokane, WA Silver Mountain Resorts 10/17/2012 4 Not Relevant to me Offensive Product Imaging Product has misleading claims
    Product C Mary Jane Seattle, WA Student 11/01/2012 1 Other - I like it.


    What I'm trying to do, though, is create a row for each survey result in which all the respondent data stays the same, but a new line is created for each column of Survey Feedback, to make my data more manageable for pivot charts, etc., i.e.:

    Product Name Address Company Survey Date Rating Survey Feedback
    Product A Bill Smith Seattle, WA Happy Foods Corporation 10/15/2012 5 Product has misleading claims
    Product A Bill Smith Seattle, WA Happy Foods Corporation 10/15/2012 5 Not Relevant to me
    Product A Bill Smith Seattle, WA Happy Foods Corporation 10/15/2012 5 Offensive Product Imaging
    Product A Bill Smith Seattle, WA Happy Foods Corporation 10/15/2012 5 Other - Product A sucks!!! I'd never buy it
    Product A John Jacobs Bellevue, WA Better Products Marketing 10/15/2012 3 Not Relevant to me
    Product A John Jacobs Bellevue, WA Better Products Marketing 10/15/2012 3 Other - I don't use this stuff.
    Product B Dave Barrett Spokane, WA Silver Mountain Resorts 10/17/2012 4 Not Relevant to me
    Product B Dave Barrett Spokane, WA Silver Mountain Resorts 10/17/2012 4 Offensive Product Imaging
    Product B Dave Barrett Spokane, WA Silver Mountain Resorts 10/17/2012 4 Product has misleading claims
    Product C Mary Jane Seattle, WA Student 11/01/2012 1 Other - I like it.


    For the life of me, I can't figure out how I can do that easily without adding individual rows under each survey line and adding them in manually - which is NOT going to happen w/ 750K+. Does anyone have any suggestions on how I might do this? I'm not very familiar w/ coding or macros, but I'm pretty good w/ formulas themselves... I'll take what I can get, though, if anyone has any suggestions. Thanks in advance!
    Bryn

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Convert rows with multiple unique columns into individual rows for pivot table managem

    Before I can truly help, is this actual data?
    Assuming it is some fields can be separated:
    Product: by seeking the first blank position and then seeking the second blank position

    State: seeking the comma
    City: seeking the comma and seeking backwardly the first blank (it does not work for cities with two words or more)
    Client: after the state and before date
    Date: seeking "/" and backwardly seeking the first blank (month), then seeking another "/" (day) and the year is 4 bytes after that "/"
    Response: if it was a multiple choice, matching each posible response by seeking if with the find function

    if you need help doing this... just say it

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1