+ Reply to Thread
Results 1 to 14 of 14

Convert Vertical Data into Horizontal for Reporting

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Question Convert Vertical Data into Horizontal for Reporting

    I'm in need of a little assistance, if someone can help. I have some vertically formatted data that I need to reformat into horizontal data so it can be used in various reporting/graphing tasks.

    I'm more of an "Access-guy", and my customer needs this to be in Excel, so please forgive me if this is a "newbie" question...

    Background...
    1. The source table/sheet has 27 columns, and the "unique key" is comprised of columns B,F,J, & X (bill_to_customer_no, ship_to_code, sku, & period)
    2. Columns A-W will effectively be identical (summarizing into a single record in the end), with column X (period) being the column that needs converted into columns.
    3. Columns Y-AA (forecast, budget, actual) will be the values that need to be parsed into the period columns
      1. Forecast (final_forecast)
      2. Budget (reference_4)
      3. Actual (actual_history)

    I've attached a sample spreadsheet with an example of how the source data will appear (Data tab), and an example of how I need the end result to be (End Result).

    Any assistance will be greatly appreciated.

    Thanks,
    7

    Attached Files Attached Files

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Convert Vertical Data into Horizontal for Reporting

    Hi Sevn,

    I've had a look at the data & it looks like you want the data in the Columns 'X' - 'AA' on the 'Data' worksheet to be transposed onto Row 3 of the 'End Result' worksheet as the data in all the other Columns in the 'Data' worksheet are the same.

    Only thing is, there are 48 fields to enter data into in the Columns between 'X' - 'BS' on the 'End Result' worksheet but only 39 fields to enter data into from the Rows between Row 2 - 40 on the 'Data' worksheet?

    Can you clarify what's going where?

    Thanks
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    ok... after your comments, it brought a few additional questions to mind, and now I have a better explanation & demo file of what's really needed.

    I've attached a new demo file, which has 24 months/columns of history, 12 months/columns of forecast, and 12 months of budget, for a total of 48 columns of History/Forecast/Budget data to be populated from the 3 columns in the "Data" sheet.

    The headers in the "End Result" sheet (12/1/2013, 1/1/2014, 2/1/2014, etc.) are dynamic, simply meaning I've written formulas using the Today(), Month(), and Year() functions to create the headers. I've done this so that each month, the headers will automatically update, and the sheet can be used as a re-useable template for this process each month.

    The sample data I provided is for 1 single BillTo/ShipTo/SKU/Period combination, and the "History/Forecast/Budget" data to be parsed out is contained in Columns Y-AA. (There are many more of these combinations, so BillTo/ShipTo/SKU/Period will be the unique/primary key)

    The row count of each BillTo/ShipTo/SKU/Period combination in the "Data" sheet will not always be 48 (the total count of "History/Forecast/Budget" data columns in the "End Result" sheet). This is because not all customers buy in every month, nor are they forecasted/budgeted every month.

    Knowing that the columns are dynamic in the "End Result", and not every BillTo/ShipTo/SKU/Period combination will contain every possible "History/Forecast/Budget" data, I envision something like this...

    I believe something would need to find each unique BillTo/ShipTo/SKU/Period combination, evaluate which dataset it belongs in (History, Forecast, or Budget), and copy the value into the cell based on the Period.

    The History & Forecast data could really be considered 1 big dataset now that I think about it, since there's no overlapping Periods.

    Again; any assistance will be greatly appreciated.

    BTW; I'm not looking for a handout, just a little direction. I'm very comfortable working with samples. I prefer to learn, rather than just copying other's work.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    anyone got any ideas that could help me on this one?

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Convert Vertical Data into Horizontal for Reporting

    Please Login or Register  to view this content.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  6. #6
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    I added your code to a button on the Home tab, and added a line to switch to Sheet3 prior to executing.

    I'm getting an "Object Required" runtime error on the line below.
    Please Login or Register  to view this content.
    Based on what I can see, this command should just be sorting the data by column X1. If that's correct, not sure what's missing.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Convert Vertical Data into Horizontal for Reporting

    Sevn,

    Is the heading in result sheet already given?
    If so, try the attached.
    If not, need the clear logic to generate the headings.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    Quote Originally Posted by jindon View Post
    Sevn,

    Is the heading in result sheet already given?
    If so, try the attached.
    If not, need the clear logic to generate the headings.
    Very impressive, and far beyond my skill set. Your code does work on the demo file you wrote it against, but I had actually uploaded a new file yesterday that has dynamic headers, and I get a subscript error when I run it on that copy. I'm going to try and modify your code to accommodate, but it honestly may be to advanced for me.

    I've attached my latest version with your code.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    with a few modifications, I was able to get your code to work with the sample data, but when ran against a larger set, it errors out on the first transpose copy.

    runtime error 1004
    the information cannot be pasted because the copy area and the paste area are not the same size and shape...

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Convert Vertical Data into Horizontal for Reporting

    Because the dates in the heading in the result is not "Date",
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    Quote Originally Posted by jindon View Post
    Because the dates in the heading in the result is not "Date",
    Please Login or Register  to view this content.
    Works like a charm, and thanks a million for your help. I just wish I understood what it's actually doing better. LOL! I've never used Scripting.Dictionary, so I have a new research topic to add to the ever growing list. :P

  12. #12
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    after a closer look at testing, it appears the SKU column isn't included in the array. I added in the column (10) to the array, and now it locks up and never completes. Is there another place I need to make a change for it to include the SKU as part of the unique id, rather than just using BillTo/ShipTo/Period?

    thanks for any assistance you may be able to provide.

    NOTE: I inserted the change I attempted to make, and colored it in red.

    Quote Originally Posted by jindon View Post
    Because the dates in the heading in the result is not "Date",
    Please Login or Register  to view this content.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Convert Vertical Data into Horizontal for Reporting

    Your alteration is correct and the code should get the result if the original code works.

    I can not replicate the problem here.

  14. #14
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: Convert Vertical Data into Horizontal for Reporting

    well, it does work on the sample data I gave you, but when ran over my real data (several thousand rows), it's running forever, and then displays "Not Responding". I'll fool with it again tonight, and make sure it's the only thing running during the test. Perhaps the additional column is using up more memory than I expected. I've added in a few other steps for progress bar updates and stuff, so I may need to comment out the extras until I figure out where the problem lies.

    thanks again.

+ 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. [SOLVED] Convert Data Vertical to Horizontal
    By dextryn in forum Excel General
    Replies: 2
    Last Post: 02-19-2013, 07:27 PM
  2. convert data horizontal to vertical
    By vorabha in forum Excel General
    Replies: 8
    Last Post: 02-10-2013, 03:53 PM
  3. Replies: 0
    Last Post: 09-27-2012, 09:52 AM
  4. convert vertical data to horizontal
    By syuk225 in forum Excel General
    Replies: 3
    Last Post: 06-14-2012, 03:58 AM
  5. [SOLVED] Convert Vertical row data into Horizontal
    By ajang in forum Excel General
    Replies: 8
    Last Post: 11-01-2010, 01:28 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