Multiply items

[Rumburak]

Hello,

I need a formula to multiply the item in column A as many times as the number in column B in the same row.
There are about 150 items in the A column.
The formula will have to list in Column C, so many items in column A, how much is the sum of the numbers in column B.

Thank you.

 

[Scott Huish]

I'm not sure exactly what you're getting at.

To multiply A1 and B1: =A1*B1
Summing column B: =SUM(B:B)

If this is not what you need can you please explain further and perhaps show a sample of your data and expected results.

 

[Rumburak]

Thank you for replay Hui,

In column A is item like MKI, IXSC MMG
In column B are only numbers.

Say in A2 is MKI and in B2 is 3 then I want in column C, in C2=MKI, in C3=MKI, in C4=MKI (MKI 3 times)
in A3 is IXSC and in B3 is - say - 2 then in C5=IXSC and in C6=IXSC (IXSC 2 times)

So it will look like:
MKI......3.......MKI
IXSC.....2......MKI
MMG.....1......MKI
....................ISXC
....................ISXC
....................ISXC
....................ISXC
....................MMG

Thank you.

 

[Scott Huish]

I don't know about a formula but this macro will do that:

Sub DuplicateItems()
Dim c As Long, x As Long, a As Range
c = 2 'Starting Row for data
For Each a In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    For x = 1 To Range("B" & a.Row)
        Range("C" & c) = a
        c = c + 1
    Next x
Next a
End Sub

 

[Rick Rothstein]

If the output to Column C will never exceed 65,535 total rows, here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub DuplicateItems()
  Dim LR As Long, RwSum As Long
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  RwSum = Application.Sum(Columns("B"))
  Range("C1:C" & RwSum) = Application.Transpose(Split(Join(Evaluate("TRANSPOSE(IF({1},REPT(A1:A" & LR & "&CHAR(1),B1:B" & LR & ")))"), ""), Chr(1)))
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 (DuplicateItems) 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.

 

[Rumburak]

Thank you so much, both of you, but I need a formula.

 

[Peter_SSs]

C2 is stand-alone
C3 copied down.

Excel Workbook
	A	B	C
1	So it will look like:
2	MKI	3	MKI
3	IXSC	2	MKI
4	MMG	1	MKI
5			IXSC
6			IXSC
7			MMG
8
Repeats

 

[bosco_yip]

But, Peter's formula will fail if quantity =0,

for example: A3 ="IXSC", B3 =0

The formula still return "IXSC" in C5

Regards
Bosco

 

[Peter_SSs]

But, Peter's formula will fail if quantity =0,

Quite true, but so far we haven't seen that as a possibility with the OP's data, so I don't think we worry about that until we see if it is a problem. Just like I'm sure Rick wouldn't yet worry about the fact that his code fails if one of the numbers in column B is, say, 100,000.