Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Dynamic List from Table

  1. #1
    New Member TheRogue's Avatar
    Join Date
    Aug 2019
    Location
    Sarasota, FL
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic List from Table

    I have a table which lists types of soda & the quantities on hand.
    I'm trying to create a dynamic list that would put each one on a new row (skipping zero quantities). It would list each, the # of time specified & then move on to the next value. The result list for this table should look something like:
    COLA
    COLA
    COLA
    GRAPE
    GRAPE
    CHERRY
    CHERRY
    CHERRY
    GINGER ALE
    ORANGE
    ORANGE

    COUNT ITEM
    3 COLA
    2 GRAPE
    3 CHERRY
    0 DIET
    1 GINGER ALE
    2 ORANGE
    0 LEMON

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

    Cool Re: Dynamic List from Table

    with PowerQuery

    COUNT ITEM ITEM
    3
    COLA COLA
    2
    GRAPE COLA
    3
    CHERRY COLA
    0
    DIET GRAPE
    1
    GINGER ALE GRAPE
    2
    ORANGE CHERRY
    0
    LEMON CHERRY
    CHERRY
    GINGER ALE
    ORANGE
    ORANGE


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        List = Table.AddColumn(Source, "Custom", each {1..[COUNT]}),
        Expand = Table.ExpandListColumn(List, "Custom"),
        Filter = Table.SelectRows(Expand, each ([Custom] <> null)),
        ROC = Table.SelectColumns(Filter,{"ITEM"})
    in
        ROC
    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. #3
    New Member TheRogue's Avatar
    Join Date
    Aug 2019
    Location
    Sarasota, FL
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic List from Table

    Thank you, this will work; but I was trying to find a way to do it w/ formulae instead of VBA.

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

    Default Re: Dynamic List from Table

    this is NOT vba but M-code for PowerQuery
    Last edited by sandy666; Aug 3rd, 2019 at 08:35 PM.
    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. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Dynamic List from Table

    Welcome to the MrExcel board!

    Quote Originally Posted by TheRogue View Post
    I was trying to find a way to do it w/ formulae
    Formulas in B2:C2 copied down as far as you might need.

    Note that another non-formula approach is to use Excel's built-in Pivot Table feature (on the Insert ribbon tab) - results show in columns E:F below.

    List & Count

    ABCDEFG
    1ITEMCOUNTITEM Row LabelsCount of Item
    2COLA3COLA CHERRY3
    3COLA2GRAPE COLA3
    4COLA3CHERRY GINGER ALE1
    5GRAPE1GINGER ALE GRAPE2
    6GRAPE2ORANGE ORANGE2
    7CHERRY Grand Total11
    8CHERRY
    9CHERRY
    10GINGER ALE
    11ORANGE
    12ORANGE
    13

    Spreadsheet Formulas
    CellFormula
    B2=IF(C2="","",COUNTIF(A$2:A$100,C2))
    C2=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 3rd, 2019 at 10:13 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member TheRogue's Avatar
    Join Date
    Aug 2019
    Location
    Sarasota, FL
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic List from Table

    Peter - Thank you for your response. The Data in columns B&C are my starting point (that's the data provided), Column A is the result that I'm trying to achieve. However, I need to do it in a way that is dynamic (so that if one of the quantities in column B are changed, the List in column A automatically adds or removes entries, as appropriate.

  7. #7
    New Member TheRogue's Avatar
    Join Date
    Aug 2019
    Location
    Sarasota, FL
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic List from Table

    I see the confusion now. I'm using Desktop Excel 2010. It does not have the PowerQuery feature.

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

    Default Re: Dynamic List from Table

    Quote Originally Posted by TheRogue View Post
    I see the confusion now. I'm using Desktop Excel 2010. It does not have the PowerQuery feature.
    but you can download free add-in from MS site
    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!

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic List from Table

    Quote Originally Posted by sandy666 View Post
    but you can download free add-in from MS site
    Only if the OP has the Pro plus version of 2010.
    Last edited by MARK858; Aug 4th, 2019 at 12:10 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  10. #10
    New Member TheRogue's Avatar
    Join Date
    Aug 2019
    Location
    Sarasota, FL
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic List from Table

    I really thought that the answer to this was going to be a simple array formula that I was over-looking, b/c I'm rusty & it's been a long time since I've had use of an array formula.

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
  •