Enumeration of rows based on given counts

Hello all. Long time Excel user and programmer, but this problem has me stumped at the moment, and I'm hoping one of you has experience which can get the cart rolling again. My basic problem can be broken down to the following. Say I have a table which has a column for items and another column for quantity of those items:

Name ..... Qty
Apple ...... 2
Grape ...... 1
Orange .... 3

What I'd like to do is generate a dynamic list of rows based on this table as follows:

Apple 1
Apple 2
Grape 1
Orange 1
Orange 2
Orange 3

Once I have this information in rows, it would be trivial to manipulate it further, but generating this table without making assumptions about the numerical quantities is proving to be difficult.

My other constraint is that it must be directly portable via Excel 2011 for Mac, so I am barred from most programmatic solutions like VBA. Generally, Pivot tables can be used to do this in reverse along with some string extraction, but going backwards seems more difficult.

Any ideas?

Welcome to the Forum nilsonj, How about uploading a sample data for doing a pivot table. I think i can try but i am not too sure.
Best Regards/VKS

Here's an example sheet with two example tables and transformations.

Enumeration of Rows Based on Given Counts.xlsx

Hi nilsonj,

Afraid I'm unsure about the compatibility criterion, but this (rather inelegant) array formula will do the trick in Excel. Assuming your data is in A2:B4, enter the below array formula in C2 and copy down as required (i.e. to as many rows as equals the sum of quantities). Extend the ":$B$4" part of ranges as required.

=INDEX($A$2:$B$4,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,ROW(INDIRECT("1:"&COUNT($B$2:$B$4))),1))>=ROWS($A$2:$A2),0),1)&" "&ROWS($A$2:$A2)-IF(ROW($B$2)+MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,ROW(INDIRECT("1:"&COUNT($B$2:$B$4))),1))>=ROWS($A$2:$A2),0)-2<=ROW($B$2),0,SUM($B$2:INDIRECT("$B$"&ROW($B$2)+MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,ROW(INDIRECT("1:"&COUNT($B$2:$B$4))),1))>=ROWS($A$2:$A2),0)-2)))

Regards

Thanks for your quick reply, XOR LX. I'm churning through what you posted now. I'll let you know shortly whether that solution will suit my purposes. Inelegant is an understatement. Did you write that on the fly, or was that collected from something else you had done in the past?

And I have yet to verify this is a working formula, but by my count you may be missing a closing parenthesis.

Hello!

I'm also struggeling with the same problem.
I tried to use the formula that XOR LX suggested, but it doesn't seem to work. There seems to be something wrong with the last COUNT expression. Can you please help??

And explain it maybe?

Thank you in advance!

Hi,

nilsonj - Have re-checked and appears that all the necessary parentheses are there. It was part on the fly and part borrowed from something I'd done in the past, as with most of my Excel formulas! Hence the (extreme) inelegance, which I'm sure could be remedied by someone with better Excel skills than myself. Still, it works as far as I can tell (I added Pears....7 to the bottom of your list and re-tested, amending the range parameter as necessary, and all good). Let me know how you get on, though I'm sure you are hoping for something a bit less cumbersome!

Linda83 - Works fine for me. Do you have the data and formula in the cells as mentioned? Have you entered the formula as an array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER)?

Hey!
Thank you for the fast response!
Because I have a Dutch version of Excel, I needed to translate some functionnames and the commas into ;
This is the result:
=INDEX($A$2:$B$4;VERGELIJKEN(WAAR;SUBTOTAAL(9;VERSCHUIVING($B$2;0;0;RIJ(INDIRECT("1:"&AANTAL($B$2:$B$4)));1))>=RIJEN($A$2:$A2);0);1)&" "&RIJEN($A$2:$A2)-ALS(RIJ($B$2)+VERGELIJKEN(WAAR;SUBTOTAAL(9;VERSCHUIVING($B$2;0;0;RIJ(INDIRECT("1:"&AANTAL($B$2:$B$4)));1))>=RIJEN($A$2:$A2);0)-2<=RIJ($B$2);0;SOM($B$2:INDIRECT("$B$"&RIJ($B$2)+VERGELIJKEN(WAAR;SUBTOTAAL(9;VERSCHUIVING($B$2;0;0;RIJ(INDIRECT("1:"&AANTAL($B$2:$B$4)));1))>=RIJEN($A$2:$A2);0)-2)))
It gives an error on the last "AANTAL" of "COUNT" in your function. I dont know why?!
example.xls

Linda,

Your issue appears to be rather that, using Excel 2003, this formula currently exceeds the maximum allowable level of 'nesting'. I am looking for a more concise version of the formula that will remedy this.

Here's a solution which doesn't use array formulae, but does use a helper column (C).

Put zero in C3 and then this formula in C4:

=SUM(B$4:B4)

Copy this down to the bottom of your table (C6) to give cumulative totals. Then you can use this formula in D4:

=IF(ROWS($1:1)>MAX($C$3:$C$6),"",INDEX(A$4:A$6,MATCH(ROWS($1:1)-1,$C$3:$C$6))&" "&ROWS($1:1)-INDEX($C$3:$C$6,MATCH(ROWS($1:1)-1,$C$3:$C$6)))

then copy down as far as you need to.

I've also applied this to your second example, with amended formulae. Additions to your file shown in yellow.

Hope this helps.

Pete

Great!! Thank you