Multiply text string by number resulting in list

[immikefazz]

Hello!

Using Excel 2010...

I have a list of names (text string) in a column (A) that have associated whole numbers in another column (B). What I would like to do is create a formula that multiplies the text string in cell A2 (for ex.) by the whole number in B2 and return a "list" of the text string in A2 equal to the whole number by which it is multiplied.

Here's a visual example of the results I want to achieve:

Using the above example, I would like to end up with a list in column D with 159 total rows with the text contents corresponding to the whole number in column B.

Any and all help is greatly appreciated. Thank you for your time.

 

[Jonmo1]

Welcome to the board..
That seems a bit to complicated for a formula.
But here's a macro that will do it.

Sub Test()
Dim lr As Long, nm As Long, drow As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For nm = 2 To lr
    drow = Cells(Rows.Count, "D").End(xlUp).Row
    For i = 1 To Cells(nm, "B")
        Cells(drow, "D").Offset(i, 0).Value = Cells(nm, "A").Value
    Next i
Next nm
End Sub

 

[barry houdini]

Try this formula in D2 copied down

=IF(ROWS(D$2:D2)>SUM(B$2:B$6),"",LOOKUP(ROWS(D$2:D2)-1,SUBTOTAL(9,OFFSET(B$1,0,0,ROW(B$2:B$6)-ROW(B$2)+1)),A$2:A$6))

 

[Jonmo1]

Try this formula in D2 copied down

=IF(ROWS(D$2:D2)>SUM(B$2:B$6),"",LOOKUP(ROWS(D$2:D2)-1,SUBTOTAL(9,OFFSET(B$1,0,0,ROW(B$2:B$6)-ROW(B$2)+1)),A$2:A$6))

All I can say is...Wow.
ray:

 

[hiker95]

immikefazz ,


Sample raw data:

Excel Workbook
	A	B	C	D
1	Name	Transactions
2	ANDY TAYLOR	3
3	JOHN TAYLOR	11
4	NICK RHODES	40
5	ROGER TAYLOR	51
6	SIMON LEBON	54
7
8
9
10
11
12
13
14
15
16
17
Sheet1

After the macro (not all the rows are shown):

Excel Workbook
	A	B	C	D
1	Name	Transactions		Results
2	ANDY TAYLOR	3		ANDY TAYLOR
3	JOHN TAYLOR	11		ANDY TAYLOR
4	NICK RHODES	40		ANDY TAYLOR
5	ROGER TAYLOR	51		JOHN TAYLOR
6	SIMON LEBON	54		JOHN TAYLOR
7				JOHN TAYLOR
8				JOHN TAYLOR
9				JOHN TAYLOR
10				JOHN TAYLOR
11				JOHN TAYLOR
12				JOHN TAYLOR
13				JOHN TAYLOR
14				JOHN TAYLOR
15				JOHN TAYLOR
16				NICK RHODES
17				NICK RHODES
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExpandList()
' hiker95, 02/06/2013
' http://www.mrexcel.com/forum/excel-questions/684025-multiply-text-string-number-resulting-list.html
Dim c As Range, nr As Long
Cells(1, 4) = "Results"
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  nr = Range("D" & Rows.Count).End(xlUp).Offset(1).Row
  Cells(nr, 4).Resize(c.Offset(, 1).Value) = c
Next c
Columns(4).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ExpandList macro.

 

[immikefazz]

You are all amazing and I truly am not worthy.

Each one you of provided me with the results I needed. I cannot thank you enough!

 

[hiker95]

immikefazz,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.