Dynamic List from Table

TheRogue

New Member
Joined
Aug 3, 2019
Messages
23
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

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

<colgroup><col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> </colgroup><tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
with PowerQuery

COUNTITEMITEM
3​
COLACOLA
2​
GRAPECOLA
3​
CHERRYCOLA
0​
DIETGRAPE
1​
GINGER ALEGRAPE
2​
ORANGECHERRY
0​
LEMONCHERRY
CHERRY
GINGER ALE
ORANGE
ORANGE

Code:
[SIZE=1]// 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[/SIZE]
 
Upvote 0
Thank you, this will work; but I was trying to find a way to do it w/ formulae instead of VBA.
 
Upvote 0
Welcome to the MrExcel board!

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.

Excel Workbook
ABCDEFG
1ITEMCOUNTITEMRow LabelsCount of Item
2COLA3COLACHERRY3
3COLA2GRAPECOLA3
4COLA3CHERRYGINGER ALE1
5GRAPE1GINGER ALEGRAPE2
6GRAPE2ORANGEORANGE2
7CHERRYGrand Total11
8CHERRY
9CHERRY
10GINGER ALE
11ORANGE
12ORANGE
13
List & Count
 
Last edited:
Upvote 0
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.
 
Upvote 0
I see the confusion now. I'm using Desktop Excel 2010. It does not have the PowerQuery feature.
 
Upvote 0
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. :confused:
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top