Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 32

Thread: Can I unpivot multiple non-contiguous rows to columns?

  1. #21
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Thanks. I've tried everything suggested an its not working.

    Can you please share the M code you use so that I can attempt to recreate it?

    Thanks.

  2. #22
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    refresh thread and read post#20 again
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #23
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    I was able to download from post#20.

    Thanks for you patience.

  4. #24
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Welcome
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #25
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Quote Originally Posted by sandy666 View Post
    check the file from post#11
    Now it contain Filter table where you can define one or two or even all Tasks then Refresh green table
    if you want remove any Task from the Filter table just delete value or choose blank position from the top of the DataValidation List
    Each change requires refreshing the green table
    I am connecting to a sharepoint directory for the query, it isn't "my" data; I need to get the pivotable list to merge with another data set, so I can't add in a filter sheet.
    It looks like you have a separate query for each week and then you append them all at the end.
    Since I have 6 other substantially similar schedules to bring in, do I just need to spam that query 24 times, one for each sheet involved? I was on a similar track initially, but was hoping there was a better way to do it.

  6. #26
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Can I unpivot multiple non-contiguous rows to columns?

    I can do all in a single Query but:
    - it will be many lines of the code
    - it will taking time to read and understood
    - you posted only 4 tables (not proper organised and darkened with colors, which, IMO are unnecessary)
    - filter in the sheet was added to make it easier (no problem, you can delete filter in sheet and filter QueryTable and use Filter in PQ Editor)
    - you didn't say anything about how you want to resolve this problem so I did all my best (ops, maybe not all ). I asked about the expected result and saw nothing
    - on the end you didn't say where are the file(s), what the structure is, how you will use it and some other things. PQ is NOT a general solution but tailored to the specific problem

    Do you have any idea? then show off
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #27
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Now you can see PivotTable only (file in post#11)
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #28
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Quote Originally Posted by sandy666 View Post
    I can do all in a single Query but:
    - it will be many lines of the code
    Maybe pointing in the general direction, or M for the tricky parts?

    Quote Originally Posted by sandy666 View Post
    - it will taking time to read and understood
    I understand M ok.

    Quote Originally Posted by sandy666 View Post
    - you posted only 4 tables (not proper organised and darkened with colors, which, IMO are unnecessary)
    This is the data as I get it. Its not my data, I've been asked to re-purpose it. I agree it is ugly and I wouldn't have done it that way.

    Quote Originally Posted by sandy666 View Post
    - on the end you didn't say where are the file(s), what the structure is, how you will use it and some other things. PQ is NOT a general solution but tailored to the specific problem
    Once I have the list of workers, tasks, and dates I will add an index. I will take a different set of data (forms in the imaging system) add another index and a new mod column to rotate the index from the workers (number of workers/day determined by a fn) and merge the forms with the workers to generate work assignments.


    Quote Originally Posted by sandy666 View Post
    Do you have any idea? then show off
    No, I don't have any ideas. The only way I could get it to work was to do a separate query for each sheet. Every time I tried to do it with multiple sheets imported, I could get all of the workers and their tasks, but only the dates for the first sheet. This will work, but if there is a way to do it in one query, or even one query/regional schedule I would be interested. I wasn't sure it was even possible to do it in one query.
    I didn't mean to come across as rude or ungrateful, nothing wrong with coming up with similar solutions.

  9. #29
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Quote Originally Posted by Mark_E View Post
    This will work, but if there is a way to do it in one query, or even one query/regional schedule I would be interested.
    Sure, if I'll take more time I'll try. Probably it will be in famous post#11
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  10. #30
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    As I said, check post#11

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •