# Automatically inserting values equal to the amount of times identified in one cell...... HELP!!!

#### rakshop

##### New Member
HI All

Another curly one that I cant seem to get my head around. I have a value (the value) in one cell, and I want that value to equal the value in another set of cells equal to the the amount of times (the multiplier) that is in a second cell.

Here is an example. The value “4” is in A1. The multiplier “5” is in A2. What I am trying to achieve is to have the value of “4” put into row 3 the amount of times equal to A2 (IE 5 times). Now I know I could do this manually, but I was wondering whether there was a way to do this automatically?

 A B C D E F G 1 4 2 5 3 4 4 4 4 4

<tbody>
</tbody>

Any suggestions???

Thanks

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### T. Valko

##### Well-known Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

Try this...

Data Range
 A​ B​ C​ D​ E​ F​ G​ 1​ 4​ 2​ 5​ 3​ 4​ 4​ 4​ 4​ 4​ 4​ ------​ ------​ ------​ ------​ ------​ ------​ ------​

This formula entered in B3:

=IF(COLUMNS(\$B3:B3)>\$A2,"",\$A1)

Copy across until you get blanks.

#### rakshop

##### New Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

GOLD!!!!!!!! Excellent thank you!

Try this...

Data Range
 A​ B​ C​ D​ E​ F​ G​ 1​ 4​ 2​ 5​ 3​ 4​ 4​ 4​ 4​ 4​ 4​ ------​ ------​ ------​ ------​ ------​ ------​ ------​

<tbody>
</tbody>

This formula entered in B3:

=IF(COLUMNS(\$B3:B3)>\$A2,"",\$A1)

Copy across until you get blanks.

#### My Aswer Is This

##### Well-known Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Range("A2").Value
Range("A3").Resize(, 20).ClearContents
Range("A3").Resize(, ans).Value = Range("A1").Value
End If
End Sub``````

Last edited:

#### T. Valko

##### Well-known Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

You're welcome. Thanks for the feedback!

#### rakshop

##### New Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

HI,

Sorry to bug you again but when I started to apply some of the more complex arrangements, the references needed to change depending on the row and now I am getting stuck again. For example: the multiplier is on the top row and the values are from row 2 down

 Multiplier 5 3 4 What I want to achieve Value 6 2 8 6 6 6 6 6 2 2 2 8 8 8 8 3 2 3 3 3 3 3 3 2 2 2 3 3 3 3 2 1 1 2 2 2 2 2 1 1 1 1 1 1 1

<colgroup><col span="17"></colgroup><tbody>
</tbody>

I want to extend the formula both laterally and vertically as more values and multipliers are added without having to manually adjust the formula each time. But I cant use shft+ctrl because of the formula \$ references are required in both the row and column depending on when i move down or across the worksheet. Hence i cant do both easily. Is there any way around this????

Thanks for your help to date, its much appreciated!

rakshop

You're welcome. Thanks for the feedback!

#### T. Valko

##### Well-known Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

I think you'll need a VBA procedure for that.

I'm not much of a programmer so I can't help you with that.

#### rakshop

##### New Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

No worries thank you for your help!

I think you'll need a VBA procedure for that.

I'm not much of a programmer so I can't help you with that.

#### T. Valko

##### Well-known Member
Re: Automaticalyl inserting values equal to the amount of times identified in one cell...... HELP!!!

Here's a formula method but it's fairly complicated.

Data Range
 A​ B​ C​ D​ E​ F​ G​ H​ 1​ 2​ 3​ 2​ 2​ 6​ 2​ 8​ 3​ 3​ 2​ 3​ 4​ 5​ 6​ 6​ 2​ 2​ 2​ 8​ 8​ 6​ 3​ 3​ 2​ 2​ 2​ 3​ 3​ 7​ -----​ -----​ -----​ -----​ -----​ -----​ -----​ -----​

This array formula** entered in A5:

=IF(COLUMNS(\$A5:A5)>SUM(\$A\$1:\$C\$1),"",INDEX(\$A2:\$C2,MIN(IF(SUBTOTAL(9,OFFSET(\$A\$1,,,,COLUMN(\$A2:\$C2)-COLUMN(\$A2)+1))>=COLUMNS(\$A5:A5),COLUMN(\$A2:\$C2)-COLUMN(\$A2)+1))))

** 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 across until you get blanks then down as needed.

Replies
3
Views
341
Replies
3
Views
370
Replies
0
Views
195
Replies
1
Views
473
Replies
7
Views
239

1,191,092
Messages
5,984,600
Members
439,896
Latest member
SquareCare

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back