Convert Cell Value in a range of cells

[ioncila]

Hi
I have this table
Is there a way to convert cell value in col A in a range of cells in col C based on col B Value?
Thanks in advance
Ioncila

A	B	C
1	6	1
8	2	1
9	3	1
11	4	1
12	5	1
16	2	1
20	4	8
24	3	9
25	3	9
		9
		11
		11
		11
		11
		and so on...

[Bob Phillips]

Not clear. Do you want to change A or C, and what is the rule that determines what it is changed to?

[ioncila]

My table is A and B columns. I want (wish) to change A in C, based in B values, assuming that each cell represents 1 unit.
I'm just trying to know if it is possible trough formulae.

Thanks
Ioncila

[ashleyuk1984]

I still don't understand...
Take a screenshot of... BEFORE, and AFTER.
Then we can make some sense of your question

[GarysStudent]

Give this a try:

Sub ColumnCBuilder()
    Dim N As Long
    Dim K As Long
    Dim L As Long, Kount As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    K = 1
    For L = 1 To N
        v = Cells(L, 1).Value
        Kount = Cells(L, 2).Value
        For j = 1 To Kount
            Cells(K, "C").Value = v
            K = K + 1
        Next j
    Next L
End Sub

[mancubus]

@GarysStudent
with one loop and one variable:

Sub ColumnCBuilder()
    Dim i As Long
    
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(Cells(i, 2).Value).Value = Cells(i, 1).Value
    Next i
    Range("C1").Delete shift:=xlUp
End Sub

[ioncila]

Hi
Thanks for all of your suggestions.
I know it works through VBA.
But I was trying if it can be with formula.

[ioncila]

Up until now, I have reached this:
=INDEX($A1$A100,CEILING(ROW()/$B$1,1))
I don't know how to change reference to B2 when number of rows reach B1 Value

[mancubus]

shoud have stated that in the opening post, ioncila.post 21 here may help:http://www.excelforum.com/excel-gene...2.htmlmanually enter the first value (A1 value which is 1) in C1.enter the formula in C2 and copy down

=IF(COUNTIF($C$1:C1,C1)=INDEX(B:B,MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,0)+1),C1)

[mancubus]

should have stated that in the first post, ioncila. ----- post 21 here may help: ----- http://www.excelforum.com/excel-gene...2.htmlmanually ----- insert the first value (A1 value, which is 1) in C1 manually. ----- insert below formula in C2 and copy down. -----

=IF(COUNTIF($C$1:C1,C1)=INDEX(B:B,MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,0)+1),C1)

[ioncila]

should have stated that in the first post, ioncila. ----- post 21 here may help: ----- http://www.excelforum.com/excel-gene...2.htmlmanually ----- insert the first value (A1 value, which is 1) in C1 manually. ----- insert below formula in C2 and copy down. -----

=IF(COUNTIF($C$1:C1,C1)=INDEX(B:B,MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,0)+1),C1)

Thank you very much for your suggestion. However, it doesn't work for the entire list (from value 20 in col A, it returns #N/D). Unfortunelly, the link you sent returns to "page not found".

Meanwhile, I have found this link http://forum.chandoo.org/threads/to-...f-times.12985/ in google and I think I have solved the issue.

Thanks again
Ioncila

[mancubus]

you are welcome. another way: http://www.mrexcel.com/forum/lounge-...ml#post1176217

=IF(ROW()-ROW(B$2)< SUM(B$2:B$5),LOOKUP(ROW()-ROW(B$2),SUBTOTAL(9,OFFSET(B$1,,,ROW(B$2:B$5)-ROW(B$2)+1,)),A$2:A$5),"")

[ioncila]

However, it doesn't work for the entire list (from value 20 in col A, it returns #N/D).

My mistake. My apologies for the wrong information. Your formulas works as fine as the one I have found in Chandoo link. I didn't place it with right references.

you are welcome. another way: http://www.mrexcel.com/forum/lounge-...ml#post1176217

For this one, I will try it later.
Anyways, I am very satisfied with the solutions in this thread.
Thank you very much
Ioncila