Formula for duplicating cells

[majesticgiftware]

Hi

I have 2 columns with info. The first column is a quantity and the second is an item number. I am looking for a formula that will create new cells with item numbers in the quantities that are in the first column.

Any help will be greatky appreciated.

 

[Tom Urtis]

What does this mean exactly:
"I am looking for a formula that will create new cells with item numbers in the quantities that are in the first column"?

Formulas do not create new cells, they only return results in the cell they occupy.

Maybe you need a pivot table or a SUMIF or SUMPRODUCT or something else. Try explaining with examples of your data and which cells that data is in, and what your expected results are based on what criteria logic.

 

[bebo021999]

Hi

I have 2 columns with info. The first column is a quantity and the second is an item number. I am looking for a formula that will create new cells with item numbers in the quantities that are in the first column.
Any help will be greatky appreciated.

I wonder if you want a list of unique item numbers in a column and sum of their quantities?

 

[majesticgiftware]

Sorry if I was unclear. Here is an example.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Column 1 Column 2 Column 3
Qty Item Formula
3 Pen Pen
3 Watch Pen
3 Plate Pen
Watch
Watch
Watch
Plate
Plate
Plate
My objective is to mutiply items by the quantity and this would be created in the column containing the formula. Is this possible?

I can't get the columns to display properly here but the last 3 watches and the last 3 plates should be under the 3 pens on the right sid in column 3

 

[bebo021999]

Sorry if I was unclear. Here is an example.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Column 1 Column 2 Column 3
Qty Item Formula
3 Pen Pen
...

With
3 Pen Pen
in Column 1
So you want like this in Column 3 ?:
Pen Pen
Pen Pen
Pen Pen

 

[majesticgiftware]

I wonder if you want a list of unique item numbers in a column and sum of their quantities?

I would like the same item numbers but multiplied by the quantity of column 2.

 

[majesticgiftware]

With
3 Pen Pen
in Column 1
So you want like this in Column 3 ?:
Pen Pen
Pen Pen
Pen Pen

3 pen

so the results would be
pen
pen
pen

 

[T. Valko]

Sorry if I was unclear. Here is an example.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Column 1 Column 2 Column 3
Qty Item Formula
3 Pen Pen
3 Watch Pen
3 Plate Pen
Watch
Watch
Watch
Plate
Plate
Plate
My objective is to mutiply items by the quantity and this would be created in the column containing the formula. Is this possible?

I can't get the columns to display properly here but the last 3 watches and the last 3 plates should be under the 3 pens on the right sid in column 3

Try this...

Book1
	A	B	C
2	3	Pen	Pen
3	3	Watch	Pen
4	3	Plate	Pen
5	_	_	Watch
6	_	_	Watch
7	_	_	Watch
8	_	_	Plate
9	_	_	Plate
10	_	_	Plate
11	_	_
Sheet1

Create this named formula:

• Name: Array
• Refers to: =ROW(INDIRECT("1:3"))

Then, this array formula** entered in C2:

=IF(ROWS(C$2:C2)>SUM(A$2:A$4),"",INDEX(B$2:B$4,MIN(IF(SUBTOTAL(9,OFFSET(A$2,,,Array))>=ROWS(C$2:C2),Array))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

The named formula, Array, refers to the number of items in the list. In your list there are 3 items: Pen, Watch, Plate. So:

=ROW(INDIRECT("1:3"))

 

[Peter_SSs]

Try using some helper cells like this.

Formula in E2 copied down at least as far as the data in columns A:B.
Formula in C2 copied down as far as you are ever likely to need (at least as many rows as the value in F1, but it can be further)

Excel Workbook
	A	B	C	D	E	F
1	Qty	Item	Formula		Cum Qty	10
2	3	Pen	Pen		1
3	4	Watch	Pen		4
4	3	Plate	Pen		8
5			Watch
6			Watch
7			Watch
8			Watch
9			Plate
10			Plate
11			Plate
12
Replicate Values

 

[majesticgiftware]

Try using some helper cells like this.

Formula in E2 copied down at least as far as the data in columns A:B.
Formula in C2 copied down as far as you are ever likely to need (at least as many rows as the value in F1, but it can be further)

Excel Workbook
	A	B	C	D	E	F
1	Qty	Item	Formula		Cum Qty	10
2	3	Pen	Pen		1
3	4	Watch	Pen		4
4	3	Plate	Pen		8
5			Watch
6			Watch
7			Watch
8			Watch
9			Plate
10			Plate
11			Plate
12
Replicate Values

Worked perfectly. Thnx.