+ Reply to Thread
Results 1 to 7 of 7

Transposing monthly grouped data from multiple rows to columns

  1. 06-20-2016, 02:38 PM #1
    taskkill
    taskkill is offline
    Registered User
    Join Date
    06-17-2016
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    6

    Transposing monthly grouped data from multiple rows to columns

    Greetings Everyone!

    Hoping that all is well. I'm new to Excel VBA and tried looking up ways in transposing data from rows to column but somehow, I am lost with the coding. I humbly seek your expertise about my query. I am trying to normalize the data set in daily tab (attached workbook) and use this data in pivot.
    The groupings are Domestic, Local, International. Second group (2nd to the hierarchy) are PS, MS, ES, PV. Last group are A, I, X, P, T.
    Your help is greatly appreciated. Thanks in advance.


    Below is the sample output:

    Sample output.jpg


    Screenshot below is my original data(apologies if the img is too long) :

    Original file.jpg



    Cheers!
    Attached Files Attached Files
    Last edited by taskkill; 06-21-2016 at 01:50 AM.
    Reply With QuoteRegister To Reply
  2. 06-21-2016, 03:41 AM #2
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Transposing monthly grouped data from multiple rows to columns

    Maybe :

    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important
    Reply With QuoteRegister To Reply
  3. 06-21-2016, 04:28 AM #3
    taskkill
    taskkill is offline
    Registered User
    Join Date
    06-17-2016
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    6

    Re: Transposing monthly grouped data from multiple rows to columns

    karedog, you're amazing!!! Your code worked.

    We need to do a little adjustment though.

    In the Month's column, there are dates that are more than the normal calendar days (more than 30 or 31). Also there are dates that are not formatted.
    I think, we just need to add the function "=text(value,"mmm-yy")" in the code but I don't know how.

    Please see the screenshot below for your reference:
    output1.jpg
    Reply With QuoteRegister To Reply
  4. 06-21-2016, 04:55 AM #4
    taskkill
    taskkill is offline
    Registered User
    Join Date
    06-17-2016
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    6

    Re: Transposing monthly grouped data from multiple rows to columns

    Oh! karedog, please disregard the dates that are more than the normal calendar days (more than 30 or 31), the first circle in the screenshot. The error lies in my end. Sorry for that.
    Reply With QuoteRegister To Reply
  5. 06-21-2016, 07:00 AM #5
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Transposing monthly grouped data from multiple rows to columns

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 06-21-2016, 08:30 AM #6
    taskkill
    taskkill is offline
    Registered User
    Join Date
    06-17-2016
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    6

    Re: Transposing monthly grouped data from multiple rows to columns

    Yep, you solved my problem. Thank you very much, karedog.
    Reply With QuoteRegister To Reply
  7. 06-21-2016, 11:14 AM #7
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Transposing monthly grouped data from multiple rows to columns

    You are welcome. Thanks for marking the thread solved and for the rep.point.


    Regards
    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)

Similar Threads

  1. [SOLVED] Transposing multiple in columns into 3 Rows
    By dhiresh in forum Excel General
    Replies: 3
    Last Post: 03-25-2015, 05:59 AM
  2. [SOLVED] Consolidating multiple rows and transposing data across columns
    By RufusParker in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-06-2015, 01:31 PM
  3. Transpose grouped columns of multiple rows into grouped rows of fewer columns
    By Birdguy in forum Excel General
    Replies: 2
    Last Post: 01-10-2014, 09:30 PM
  4. [SOLVED] transposing multiple columns to rows
    By atiul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2012, 08:05 AM
  5. [SOLVED] Excel 2007 : Converting grouped data in multiple rows and columns to single row
    By learner_123 in forum Excel General
    Replies: 4
    Last Post: 06-29-2012, 05:56 AM
  6. Converting grouped data in multiple rows and columns to single row
    By learner_123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2012, 05:49 AM
  7. Transposing data from multiple rows into single rows & multiple columns
    By eterna24 in forum Excel General
    Replies: 1
    Last Post: 03-18-2009, 04:18 PM

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