Fill in values on in a column base on percentages of that value in another column

[sparkzilla]

Hi, I would like to fill values into a column based on the percentages of that value in another column. For example, if the first value is 10 and it has 5% of the total, then if the total number of rows is 100, then the first five will be filled in with the value 10. In the example below I assume that the result column is 100 rows deep, however the script/formula should be able to create proportional results for any number of rows. For example, if the total number of rows is 300, then we would get 15 rows of he first value (10). Thank you for your help.

Book1
	A	B	C	D
3	Value	Percentage		Result
4	10	5%		10
5	20	7%		10
6	30	20%		10
7	40	55%		10
8	50	13%		10
9		100%		20
10				20
11				20
12				20
13				20
14				20
15				20
16				30
17				30
18				30
19				and so on
Sheet1

 

[igold]

Hi sparkzilla,

Welcome to the MrExcel Forum:

I have a question... If the total number of rows is 300 as you suggest. How do we know that from looking at your data?

 

[sparkzilla]

Hi sparkzilla,

Welcome to the MrExcel Forum:

I have a question... If the total number of rows is 300 as you suggest. How do we know that from looking at your data?

Thanks! Feel free to add the number of rows as a value in another cell.

 

[igold]

With your header row as you show it on Row 3, I used Cell C3 for your number of rows. See if this is what you want...

Sub test()


    Dim ct As Long, rws As Long, lRow As Long, i As Long, nr As Long, fr As Range
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    ct = Range("C3")
    Set fr = Range("D4")
    For i = 4 To lRow
        rws = Application.WorksheetFunction.RoundUp(Cells(i, 2).Value * ct, 0)
        fr.Resize(rws) = Cells(i, 1).Value
        nr = Cells(Rows.Count, 4).End(xlUp).Row + 1
        Set fr = Range("D" & nr)
    Next
    
End Sub

 

[sparkzilla]

This is great. It works perfectly. Thank you! If I had multiple value and percentage rows on one sheet, how would I show the results on another sheet in the same worksheet?

 

[igold]

I am glad that this works for you. The only caveat, is that due to rounding, you may get more or less than the number of rows deep that is inputted.

Could you be a drop more explicit on what your new request is. I am not sure I follow what is where and where you want the output.