Dynamic List from Table

[TheRogue]

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

<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>
[TD="width: 66, align: left"]COUNT[/TD]
[TD="width: 79, align: left"]ITEM[/TD]

[TD="class: xl65"]3[/TD]
[TD="align: left"]COLA[/TD]

[TD="class: xl65"]2[/TD]
[TD="align: left"]GRAPE
[/TD]

[TD="class: xl65"]3[/TD]
[TD="align: left"]CHERRY[/TD]

[TD="class: xl65"]0[/TD]
[TD="align: left"]DIET[/TD]

[TD="class: xl65"]1[/TD]
[TD="align: left"]GINGER ALE[/TD]

[TD="class: xl65"]2[/TD]
[TD="align: left"]ORANGE[/TD]

[TD="class: xl65"]0[/TD]
[TD="align: left"]LEMON
[/TD]

</tbody>

 

[sandy666]

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]COUNT[/td][td=bgcolor:#5B9BD5]ITEM[/td][td][/td][td=bgcolor:#70AD47]ITEM[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]COLA[/td][td][/td][td=bgcolor:#E2EFDA]COLA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]GRAPE[/td][td][/td][td]COLA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]CHERRY[/td][td][/td][td=bgcolor:#E2EFDA]COLA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td]DIET[/td][td][/td][td]GRAPE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]GINGER ALE[/td][td][/td][td=bgcolor:#E2EFDA]GRAPE[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]ORANGE[/td][td][/td][td]CHERRY[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

0​

[/td][td=bgcolor:#DDEBF7]LEMON[/td][td][/td][td=bgcolor:#E2EFDA]CHERRY[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]CHERRY[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]GINGER ALE[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]ORANGE[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]ORANGE[/td][/tr]
[/table]

[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]

 

[TheRogue]

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

 

[sandy666]

this is NOT vba but M-code for PowerQuery

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	ITEM	COUNT	ITEM		Row Labels	Count of Item
2	COLA	3	COLA		CHERRY	3
3	COLA	2	GRAPE		COLA	3
4	COLA	3	CHERRY		GINGER ALE	1
5	GRAPE	1	GINGER ALE		GRAPE	2
6	GRAPE	2	ORANGE		ORANGE	2
7	CHERRY				Grand Total	11
8	CHERRY
9	CHERRY
10	GINGER ALE
11	ORANGE
12	ORANGE
13
List & Count

 

[TheRogue]

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.

 

[TheRogue]

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

 

[sandy666]

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

 

[MARK858]

but you can download free add-in from MS site

Only if the OP has the Pro plus version of 2010.

 

[TheRogue]

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.