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-general/724598-how-to-copy-cell-value-for-x-number-of-times-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-general/724598-how-to-copy-cell-value-for-x-number-of-times-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-general/724598-how-to-copy-cell-value-for-x-number-of-times-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-repeat-row-values-a-specified-number-of-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-v-2-0/153357-tricks-gurus-2.html#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-v-2-0/153357-tricks-gurus-2.html#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