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

[rakshop]

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

 

[T. Valko]

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]

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]

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

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

 

[T. Valko]

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

You're welcome. Thanks for the feedback!

 

[rakshop]

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]

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]

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]

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.