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>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,067
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]
 

TheRogue

New Member
Joined
Aug 3, 2019
Messages
23
Thank you, this will work; but I was trying to find a way to do it w/ formulae instead of VBA.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,809
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

TheRogue

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

New Member
Joined
Aug 3, 2019
Messages
23

ADVERTISEMENT

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

TheRogue

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

Watch MrExcel Video

Forum statistics

Threads
1,113,825
Messages
5,544,545
Members
410,619
Latest member
gregor222
Top