+ Reply to Thread
Results 1 to 19 of 19

Data in table format to data row with columns as second level description

  1. 06-05-2022, 04:34 AM #1
    J555
    J555 is offline
    Registered User
    Join Date
    06-05-2022
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Data in table format to data row with columns as second level description

    Hi

    I am trying to rearranges data from a normal table with rows and columns grid (salesperson Column A, Product 1 sales Colum B, Product 2 sales Colum C ) to rows having two layers of description (salesperson column A , Product name column B, sales amount column C)

    Please see attached excel for before and after

    Thank you
    Attached Files Attached Files
    Last edited by J555; 06-06-2022 at 05:30 AM.
    Reply With QuoteRegister To Reply
  2. 06-05-2022, 04:37 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    Welcome to the forum.

    Can I confirm that it is definitely Excel 2019 you have, and not MS365 (the subscription)?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-05-2022, 04:40 AM #3
    J555
    J555 is offline
    Registered User
    Join Date
    06-05-2022
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Data in table format to data row with columns as second level description



    I have MS365.

    Thanks will update my profile
    Reply With QuoteRegister To Reply
  4. 06-05-2022, 04:44 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    Ah - good. I'll have another look.

    In the meantime, a PowerQuery solution:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AliGW; 06-05-2022 at 04:48 AM. Reason: Workbook attached.
    Reply With QuoteRegister To Reply
  5. 06-05-2022, 04:55 AM #5
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Data in table format to data row with columns as second level description

    This code will do the job for you.

    Please Login or Register  to view this content.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  6. 06-05-2022, 05:35 AM #6
    J555
    J555 is offline
    Registered User
    Join Date
    06-05-2022
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Data in table format to data row with columns as second level description

    Thanks AliGW and oeldere
    It looks like a good one

    Going to try both at work and give feedback (and questions and thanks)
    Reply With QuoteRegister To Reply
  7. 06-05-2022, 05:44 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    Please do - shen you've decided, then let us know.
    Reply With QuoteRegister To Reply
  8. 06-05-2022, 07:04 AM #8
    T.I.
    T.I. is offline
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Data in table format to data row with columns as second level description

    Formulas:
    Persons:
    PHP Code: 
    =INDEX($A$5:$A$7;1+INT((ROWS(E$17:E17)-1)/COUNTA($B$4:$G$4))) 
    Products:
    PHP Code: 
    =INDEX($B$4:$G$4;1+MOD((ROWS(F$17:F17)-1);COUNTA($B$4:$G$4))) 
    Sales:
    PHP Code: 
    =INDEX($B$5:$G$7;MATCH($E17;$A$5:$A$7;);AGGREGATE(15;6;(COLUMN($B$4:$G$4)-COLUMN($B$4)+1)/(($F17=$B$4:$G$4));COUNTIFS($E$17:$E17;$E17;$F$17:$F17;$F17))) 
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-05-2022, 07:07 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    @Strogg

    Your formula need to have semi-colons replaced by commas.
    Reply With QuoteRegister To Reply
  10. 06-05-2022, 07:30 AM #10
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,417

    Re: Data in table format to data row with columns as second level description

    Pls try

    Cell A16 formula

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 06-05-2022, 07:35 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    Nice one!!!
    Reply With QuoteRegister To Reply
  12. 06-05-2022, 07:45 AM #12
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,417

    Re: Data in table format to data row with columns as second level description

    Thank you very much for ALIGW's rep.
    Last edited by AliGW; 06-05-2022 at 07:48 AM.
    Reply With QuoteRegister To Reply
  13. 06-05-2022, 10:01 AM #13
    T.I.
    T.I. is offline
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271
    @AliGW
    Semicolons in file will be changed automatically according to OS settings.
    Reply With QuoteRegister To Reply
  14. 06-05-2022, 10:02 AM #14
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Data in table format to data row with columns as second level description

    Yes, however it is worth mentioning: some people don't open attached workbooks.
    Reply With QuoteRegister To Reply
  15. 06-05-2022, 10:17 AM #15
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Data in table format to data row with columns as second level description

    Another option
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 06-06-2022, 05:41 AM #16
    J555
    J555 is offline
    Registered User
    Join Date
    06-05-2022
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Data in table format to data row with columns as second level description

    Thanks and Feedback

    To start thanks for every response. Learned a lot from all even the ones I did not use

    AliGW - PowerQuery - I used it and it worked great. Redid it as I do not just copy code as I want to try it myself
    oeldere - code - loved the code
    Strogg - I need to learn PHP, only started with Python
    wk9128 - wow nice formula, I am very impressed
    Fluff13 - also very impressed.

    Thanks again. I really apricate the help
    Reply With QuoteRegister To Reply
  17. 06-06-2022, 05:46 AM #17
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Data in table format to data row with columns as second level description

    Glad I could help.

    Thanks for marking the question solved.

    You can add reputation by clicking on the star * add reputation.


    I am always interested in which solution you choosed.
    Reply With QuoteRegister To Reply
  18. 06-06-2022, 06:48 AM #18
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Data in table format to data row with columns as second level description

    Glad to help & thanks for the feedback.
    Reply With QuoteRegister To Reply
  19. 06-06-2022, 07:24 AM #19
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,417

    Re: Data in table format to data row with columns as second level description

    @J555 You're Welcome. Glad to help . Thank You for the feedback
    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] Sorting data from single columns to table format
    By barneyrubble1965 in forum Excel General
    Replies: 8
    Last Post: 02-16-2018, 04:47 AM
  2. Moving Row-Level Output data into Columns
    By burghman787 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2017, 09:10 AM
  3. Moving Row-Level Output data into Columns
    By burghman787 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2017, 12:52 PM
  4. Apply formulas to the end of data of varying length in columns(please see description)
    By Exorbit in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-05-2012, 12:35 PM
  5. Excel 2007 : Merging Individual and Group level data - Copying multiple columns of matched data?
    By ea2146 in forum Excel General
    Replies: 1
    Last Post: 04-09-2012, 09:21 AM
  6. Count and compute rows of data with different catagories and description 2003 format
    By bsengineer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2009, 08:51 PM
  7. [SOLVED] how do I format data into columns using a pivot table report?
    By snd in forum Excel General
    Replies: 1
    Last Post: 04-24-2006, 04:10 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