Formula to expand out list

[lellery]

Hi,

I'm trying to work out a formula in which expands out a list based on 2 columns. Column A is the number of fruit, column B is the fruit and I want in column C a list of all the fruit.

e.g.


[TABLE="width: 353"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Number of Fruit[/TD]
[TD]Fruit[/TD]
[TD]Result:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Banana[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Pears[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Pinapple[/TD]
[TD]Pinapple[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cherry[/TD]
[TD]Cherry[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Grapes[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Kiwi[/TD]
[TD]Plums[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Plums[/TD]
[TD]Plums[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peach[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Watermelon[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Melon[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this possible? Any help will be appreciated!

Thanks!

 

[Rick Rothstein]

I don't have a formula solution for you; rather, I have a macro that will do what you asked for.

[table="width: 500"]
[tr]
	[td]Sub ExpandedList()
  Dim R As Long, N As Long, X As Long, Data As Variant, Result As Variant
  ReDim Result(1 To [SUM(A:A)], 1 To 1)
  Data = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For N = 1 To Data(R, 1)
      X = X + 1
      Result(X, 1) = Data(R, 2)
    Next
  Next
  Range("C2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ExpandedList) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[lellery]

Thanks for this, work great.

But I would like to have this as a formula so that if change the quantity, it automatically updates without running a macro.

This is a good quick fix though! Thanks!

I don't have a formula solution for you; rather, I have a macro that will do what you asked for.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ExpandedList()
  Dim R As Long, N As Long, X As Long, Data As Variant, Result As Variant
  ReDim Result(1 To [SUM(A:A)], 1 To 1)
  Data = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For N = 1 To Data(R, 1)
      X = X + 1
      Result(X, 1) = Data(R, 2)
    Next
  Next
  Range("C2").Resize(UBound(Result)) = Result
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ExpandedList) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.