+ Reply to Thread
Results 1 to 16 of 16

Transpose of a table with conditions

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Transpose of a table with conditions

    Dear Friends,

    I am having a table with pack nos, items & weight.
    I need to regorganise the table based on the pack no. in a row of maximum 5 items.
    The file is attched.
    I am using Excel 2016.
    Awaiting for your solutions.
    Thanks
    Jayapresad
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will provide the link this time: https://chandoo.org/forum/threads/tr...nditions.44432)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Dear Ali,
    Thanks for your pointing out the rule, which I was not aware and will not do the same.
    What I have to do now? Shall I repost the same in this forum again?
    Awaiting for your reply.

    Thanks
    Jayapresad

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    No - read post #2 FULLY, which explains everything.

    When you join any 'club', it is customary to read the rules first ...

  5. #5
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Dear Ali,
    I read fully your rules and will abide that.
    Excuse me for this time.
    Thanks
    JP

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    I used PowerQuery.

    M Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Dear AliGW,

    Thanks for your solution with Power query.
    I am not reached the stage to write code and do the conversion.

    If you can help me with formulas whic I am comfortable.

    JP

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    Time to learn a new trick, then. Excel 2016 has PowerQuery - learn to use it.

    You did not specify that it had to be a formula solution. I don't have time today to build a formula solution for you - sorry.
    Last edited by AliGW; 06-06-2020 at 06:32 AM.

  9. #9
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Quote Originally Posted by AliGW View Post
    Time to learn a new trick, then. Excel 2016 has PowerQuery - learn to use it.

    You did not specify that it had to be a formula solution. I don't have time today to build a formula solution for you - sorry.
    Dear AliGW,
    Thanks for your suggestion to learn power query.I brushed up power query and used your code and I go the solution.
    However , when I changed the data( by inreasing or decreasing the pack Nos) the error is happening. I understand the issue is due to structure of the final table.
    If we can provide a final table dtrucure as below, then this can be solved.
    Pack No: Item 1 KG 1 Item 2 KG 2 Item 3 KG 3 Item 4 KG 4 Item 5 KG 5

    Please advice me how to do modification in the m code to get the above table structure.

    Awaiting for your reply.
    Thanks
    JP

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    I will have a look if you provide a workbook that shows the problem.

  11. #11
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Quote Originally Posted by AliGW View Post
    I will have a look if you provide a workbook that shows the problem.
    Dear AliGW,
    Thanks for your response,

    Requirement
    Combine table in sheet1 & Table in Sheet2 & to get file in Sheet3 format.

    Issues
    When the data changes in tables in sheet2 & sheet3, the format changes with so many columns. How to resolve this.
    If no. of items& qty are not even to fill up to last columns, the final sheet shows error that last colmn is missing. How to resolve this.

    The ile is attached.
    Thanks
    JP
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Transpose of a table with conditions

    Sorry - not clear what you want. You mention changing the table on sheet 3, but that's the results table.

    I have no time to look at this at the moment - sorry.

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,157

    Re: Transpose of a table with conditions

    and copy pasted in a new sheet?
    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.

  14. #14
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Dear AliGW

    OK thanks
    JP

  15. #15
    Registered User
    Join Date
    01-22-2020
    Location
    Kochi
    MS-Off Ver
    2016
    Posts
    11

    Re: Transpose of a table with conditions

    Quote Originally Posted by oeldere View Post
    and copy pasted in a new sheet?
    Dear Friend
    it is not copied and pasted. Final table is an appended table with power query.
    Thanks

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,157

    Re: Transpose of a table with conditions

    I read you are suggested to use Power Query.

    Since you did not know that (Power Query), it also can be done with copy, paste the new data below sheet 1.

+ 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] Transpose Columns with Conditions
    By Gazsim in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-14-2020, 09:19 PM
  2. To transpose or not to transpose? What decides? Table / Array?
    By LIL2606 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-19-2019, 01:28 PM
  3. Replies: 4
    Last Post: 08-21-2018, 09:24 AM
  4. [SOLVED] Transpose Data with match Conditions
    By abhinavbinkar in forum Excel General
    Replies: 3
    Last Post: 06-28-2018, 05:43 AM
  5. Transpose and sum with conditions
    By anahochmanova in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2016, 11:20 AM
  6. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  7. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM

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