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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,895
Office Version
  1. 365
Platform
  1. Windows
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.
Sorry, I had misread the question. :oops:

Would a helper column (could be hidden) be acceptable?

C1 is a stand-alone formula
C2 copied down to the end of your data.
B11 copied down as far as you might ever need.

Excel Workbook
ABC
1COUNTITEM11
23COLA1
32GRAPE4
43CHERRY6
50DIET
61GINGER ALE9
72ORANGE10
80LEMON
9
10
11COLA
12COLA
13COLA
14GRAPE
15GRAPE
16CHERRY
17CHERRY
18CHERRY
19GINGER ALE
20ORANGE
21ORANGE
22
23
List
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

TheRogue

New Member
Joined
Aug 3, 2019
Messages
23
Yes. Thank you. This does exactly what I want it to. Now, I just have to see if I can "tweak" it some. (The provided data is a table. There is no problem w/ adding a helper column to the table, but the result has to live elsewhere.) You've showed me the concept (which I now understand), now I just have to apply it to my situation.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,895
Office Version
  1. 365
Platform
  1. Windows
Now, I just have to see if I can "tweak" it some. (The provided data is a table. There is no problem w/ adding a helper column to the table, ...
Try something like this


Excel Workbook
ABCDEF
1COUNTITEMHelperList
23COLA1COLA
32GRAPE4COLA
43CHERRY6COLA
50DIETGRAPE
61GINGER ALE9GRAPE
72ORANGE10CHERRY
80LEMONCHERRY
9CHERRY
10GINGER ALE
11ORANGE
12ORANGE
13
List
 

TheRogue

New Member
Joined
Aug 3, 2019
Messages
23
Thank you very much! This is going to work perfectly.
As usual, I had gotten "lost in the weeds" & was over-complicating it.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,895
Office Version
  1. 365
Platform
  1. Windows
No problem, glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,240
Members
410,779
Latest member
che55ysia
Top