+ Reply to Thread
Results 1 to 4 of 4

Restructuring Excel Data

  1. #1
    Registered User
    Join Date
    11-28-2016
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016 Pro
    Posts
    2

    Restructuring Excel Data

    I have a problem with re-structuring data in excel for the analysis I need to do. I basically get my data in the following format from the database:

    Client Identifier for Mr A.- Date of assessment - Summary score 1 from assessment - Summary score 2 from assessment - Summary score 3 from assessment - etc.
    Client Identifier for Mr A. - Second assessment date - Summary score 1 from this second assessment - summary score 2 from this second assessment - summary score 3 from this second assessment -- etc.
    Client identifier for Mr B. - Date of assessment ...


    This goes on and on. And to make matters more complicated, Mr A may be assessed at three different time points (typically 30 days apart) and Mr B may be assessed 6 times, and Mr. C may be assessed once, etc. So the amount of times is not consistent.

    And I am trying to make graphs that will have the average of all of the summary assessment scores at time 1, and then average each score at all second assessments, and then average score of the third summary score at each assessment time ... so that I can make these average of each summary score at an initial, re-assessment, etc.

    So, in short, I need the output to look like this:

    Summary score 1 average at initial, summary score 1 average at second assessment, summary score 1 average at third assessment, etc.
    Summary score 2 average at initial, summary score 2 average at second assessment, summary score 2 average at third assessment, etc.
    Summary score 3 average at initial, summary score 3 average at second assessment, summary score 3 average at third assessment, etc.

    Typicaly, I get this to this point by MANUALLY setting up the data in the following way:

    Client Identifier Mr A. - date of first assessment - summary score one, etc. - date of second assessment - Summary score two, etc. - date of third assessment - summary score three, etc.
    Client Identifier Mr. B - date of first assessment - summary score one, etc. - date of second assessment - Summary score two, etc. - date of third assessment - summary score three, etc.


    And then I can just get the average of each of these assessments at the bottom of the sheet.

    So ... does anyone know how I can automate setting up the data in this way? Or is there another way I can think about this to get what I need? The manual approach will simply not work with the size that my data is getting to.

    Thanks in advance!
    Dan

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Restructuring Excel Data

    You'll have to work out the analysis part later. For now, if you need help converting data from an inconsistent unusable format into a usable one we can try and help there.

    Attach a sample workbook. Make sure is enough sample data to demonstrate your need. Remember to desensitize the data.

    Include a BEFORE sheet and an AFTER sheet in this workbook to show the changes needed.


    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-28-2016
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016 Pro
    Posts
    2

    Re: Restructuring Excel Data

    Thank you very much for helping me. I have attached a workbook with two sheets: a before and after.
    I have also done some color coding to the data, to show the different "time points" I am trying to make out of this data.

    At the bottom of the "after" tab, you can see the table I am ultimately trying to make by moving all of this data around...
    If you have any thoughts on how to make that table without all of this data manipulation, I'd love to hear about that as well!

    Thanks so much for any insight you can offer,
    Dan
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Restructuring Excel Data

    I couldn't think of a simple way to make that dynamic enough to be usable, so spent some time writing a little macro to do it for you. This macro:

    1) Sorts the RawData by column A, then Column B. All other columns to the right will end up in the summary table, however many columns that happens to be.

    2) Creates the NewData sheet in the format you presented.

    The only difference is the summary table does not have formulas.

    Please Login or Register  to view this content.
    Drop the data into the RawData sheet and press the button to activate the macro.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. restructuring table data
    By r04mg15 in forum Excel General
    Replies: 7
    Last Post: 08-25-2016, 09:52 AM
  2. Restructuring data from single row to multiple rows
    By fruitmonkey321 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-21-2016, 09:06 AM
  3. Restructuring data
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-11-2014, 08:41 AM
  4. Restructuring data with a macro?
    By JAMatthews in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2013, 01:41 PM
  5. Restructuring Data
    By SimonNZ in forum Excel General
    Replies: 3
    Last Post: 09-22-2010, 10:24 PM
  6. restructuring data
    By Klipvis in forum Excel General
    Replies: 3
    Last Post: 10-21-2009, 05:30 AM

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