+ Reply to Thread
Results 1 to 13 of 13

Build a cumulative list of roles (headings) from a table of outlets and required numbers

  1. #1
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Build a cumulative list of roles (headings) from a table of outlets and required numbers

    Outlet Supervisor Host Bar Floor Runner
    Level 1 2 1 1 4 1
    Mezzanine 1 1 1 5 1
    Level 2 2 1 2 8 3

    Indicative list
    Supervisor
    Supervisor
    Host
    Bar
    Bar
    Floor
    Floor
    Floor
    Floor
    Runner
    Supervisor
    Host
    Bar
    Floor
    Floor
    Floor
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Welcome to the forum.

    What's the logic?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  3. #3
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Sorry the post wasn't clear. The table should be as follows:

    Attachment 651464

    With the result being
    Indicative list
    Supervisor
    Supervisor
    Host
    Bar
    Floor
    Floor
    Floor
    Supervisor
    Host
    Bar
    Floor
    Floor
    Floor
    Floor

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Still can’t fathom it - sorry.

    Try explaining in words what you are trying to achieve.

  5. #5
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    I'm trying to work out the formula to build a column of roles from the numbers required as per the table, so that the column builds down from the numbers required in each row and then builds the next row and so on, so for example if i need 1 supervisor and 2 bar in one venue then 2 supervisor and 3 bar in another it will build them like below:

    Supervisor
    Bar
    Bar
    Supervisor
    Supervisor
    Bar
    Bar
    Bar

    Does that make sense?
    Last edited by Nicnoo; 11-25-2019 at 10:57 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,051

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Please try

    J4
    Please Login or Register  to view this content.
    Regards.

  7. #7
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Hi there,
    No that didn't work, but I am not sure what the content is meant to be in L3 or is that just a blank cell?

  8. #8
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,051

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Sorry for my mistake >_<
    Plese try again.

    Please Login or Register  to view this content.
    Regards.

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,088

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Please try at J4
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or Power Query

    Open Power Query/Get and Transform. Click on New Query.
    Open blank query in the editor, launch Advanced Editor and paste in the following code.

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

  10. #10
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    THANK YOU! That is brilliant. Worked perfectly.

  11. #11
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Can you then reverse it to build the other list, such as.

    Level 1
    Level 1
    Level 1
    Level 1
    Level 1
    Level 1
    Level 1
    Level 1
    Level 1
    Mezzanine
    Mezzanine
    Mezzanine
    Mezzanine

    Or can I get away with a shorter formula for that?

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,088

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Please try at
    N4
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    O4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-29-2019 at 03:10 AM.

  13. #13
    Registered User
    Join Date
    11-24-2019
    Location
    Australia
    MS-Off Ver
    Excel 1902
    Posts
    7

    Re: Build a cumulative list of roles (headings) from a table of outlets and required numbe

    Hi there,
    Works perfectly. Thank you so much for all your help.

+ 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. Replies: 4
    Last Post: 09-20-2019, 03:53 PM
  2. Replies: 2
    Last Post: 02-23-2018, 12:12 AM
  3. Replies: 2
    Last Post: 11-12-2015, 12:54 PM
  4. Replies: 2
    Last Post: 09-24-2012, 10:39 PM
  5. Replies: 0
    Last Post: 02-10-2012, 04:02 PM
  6. Build a TOC with Headings within a table
    By Toomay in forum Word Formatting & General
    Replies: 1
    Last Post: 07-04-2011, 06:50 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