Hi all,

I have a small but anyway very interesting problem with Excel.
I am actually starting from a table with some values inside. For
example:
4
2
5

I am looking for a formula which would give me a new table like this:
4
4
4
4
2
2
5
5
5
5
5

I tried quite a few things, especially with the offset function, but
couldn't set it. Your help would be greatly appreciated!
Of course, I can't do it manually as my numbers look like 127,221 and
not 4 or 5.

Thank you,
Johan

i'm not exactly sure what you want. i listed your values in column A on sheet 1

then wrote the code to put the 1st cell's values in column B and the next cell's
values in column C and so on
maybe it will help you get started.

Sub test()
Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim cell As Range
Dim numval As Long
Dim z As Long, y As Long

Dim ws As Worksheet
z = 1
y = 2
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In ws.Range("A1:A" & lastrow)
For i = 1 To Len(cell.Value)
numval = Mid(cell.Value, i, 1)
Debug.Print numval
For j = 1 To numval
ws.Cells(z, y).Value = numval
z = z + 1
Next
Next
z = 1
y = y + 1
Next

End Sub

--


Gary


<(E-Mail Removed)> wrote in message
news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
> Hi all,
>
> I have a small but anyway very interesting problem with Excel.
> I am actually starting from a table with some values inside. For
> example:
> 4
> 2
> 5
>
> I am looking for a formula which would give me a new table like this:
> 4
> 4
> 4
> 4
> 2
> 2
> 5
> 5
> 5
> 5
> 5
>
> I tried quite a few things, especially with the offset function, but
> couldn't set it. Your help would be greatly appreciated!
> Of course, I can't do it manually as my numbers look like 127,221 and
> not 4 or 5.
>
> Thank you,
> Johan

i forgot to ask what you wanted to do if a number had a zero in it.

--


Gary


<(E-Mail Removed)> wrote in message
news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
> Hi all,
>
> I have a small but anyway very interesting problem with Excel.
> I am actually starting from a table with some values inside. For
> example:
> 4
> 2
> 5
>
> I am looking for a formula which would give me a new table like this:
> 4
> 4
> 4
> 4
> 2
> 2
> 5
> 5
> 5
> 5
> 5
>
> I tried quite a few things, especially with the offset function, but
> couldn't set it. Your help would be greatly appreciated!
> Of course, I can't do it manually as my numbers look like 127,221 and
> not 4 or 5.
>
> Thank you,
> Johan

Thx. It is not exactly what I am looking for but I can use it as a
good basis. (I actually don't need to create new columns for each
value, everything should be listed in one column)

On 11 juil, 13:48, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> i forgot to ask what you wanted to do if a number had a zero in it.
>
> --
>
> Gary
>
> <johan.geor...@gmail.com> wrote in message
>
> news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
>
> > Hi all,
>
> > I have a small but anyway very interesting problem with Excel.
> > I am actually starting from a table with some values inside. For
> > example:
> > 4
> > 2
> > 5
>
> > I am looking for a formula which would give me a new table like this:
> > 4
> > 4
> > 4
> > 4
> > 2
> > 2
> > 5
> > 5
> > 5
> > 5
> > 5
>
> > I tried quite a few things, especially with the offset function, but
> > couldn't set it. Your help would be greatly appreciated!
> > Of course, I can't do it manually as my numbers look like 127,221 and
> > not 4 or 5.
>
> > Thank you,
> > Johan

> i forgot to ask what you wanted to do if a number had a zero in it.

I have a question too... are there any other columns in your "table" and, if
so, will they need to be duplicated along with the one column you showed us?

Rick

My question is if the values look like 127,221 you are going to run out of
rows pretty quick. I presume you are using Excel 2007 ?

--

Regards,
Nigel
(E-Mail Removed)



"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:(E-Mail Removed)...
>i forgot to ask what you wanted to do if a number had a zero in it.
>
> --
>
>
> Gary
>
>
> <(E-Mail Removed)> wrote in message
> news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
>> Hi all,
>>
>> I have a small but anyway very interesting problem with Excel.
>> I am actually starting from a table with some values inside. For
>> example:
>> 4
>> 2
>> 5
>>
>> I am looking for a formula which would give me a new table like this:
>> 4
>> 4
>> 4
>> 4
>> 2
>> 2
>> 5
>> 5
>> 5
>> 5
>> 5
>>
>> I tried quite a few things, especially with the offset function, but
>> couldn't set it. Your help would be greatly appreciated!
>> Of course, I can't do it manually as my numbers look like 127,221 and
>> not 4 or 5.
>>
>> Thank you,
>> Johan
>
>

you can change the column letter to what you want

Sub test()
Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim cell As Range
Dim numval As Long
Dim z As Long
Dim ws As Worksheet
z = 1

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In ws.Range("A1:A" & lastrow)
For i = 1 To Len(cell.Value)
numval = Mid(cell.Value, i, 1)
For j = 1 To numval
ws.Cells(z, "B").Value = numval
z = z + 1
Next
Next
Next
End Sub




--


Gary


<(E-Mail Removed)> wrote in message
news:50c594ce-1f63-41f0-a55e-(E-Mail Removed)...
> Thx. It is not exactly what I am looking for but I can use it as a
> good basis. (I actually don't need to create new columns for each
> value, everything should be listed in one column)
>
> On 11 juil, 13:48, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
>> i forgot to ask what you wanted to do if a number had a zero in it.
>>
>> --
>>
>> Gary
>>
>> <johan.geor...@gmail.com> wrote in message
>>
>> news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
>>
>> > Hi all,
>>
>> > I have a small but anyway very interesting problem with Excel.
>> > I am actually starting from a table with some values inside. For
>> > example:
>> > 4
>> > 2
>> > 5
>>
>> > I am looking for a formula which would give me a new table like this:
>> > 4
>> > 4
>> > 4
>> > 4
>> > 2
>> > 2
>> > 5
>> > 5
>> > 5
>> > 5
>> > 5
>>
>> > I tried quite a few things, especially with the offset function, but
>> > couldn't set it. Your help would be greatly appreciated!
>> > Of course, I can't do it manually as my numbers look like 127,221 and
>> > not 4 or 5.
>>
>> > Thank you,
>> > Johan
>

Rick, I have only one column to create.
Nigel, I am actually using excel 2003. It is indeed possible to run
out of rows with the kind of result I expect, although in my case the
final table is around 30,000 rows.

Gary, thx for your help. I finally succeeded to get what I was looking
for.

On Jul 11, 3:00*pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> you can change the column letter to what you want
>
> Sub test()
> * * * Dim i As Long
> * * * Dim j As Long
> * * * Dim lastrow As Long
> * * * Dim cell As Range
> * * * Dim numval As Long
> * * * Dim z As Long
> * * * Dim ws As Worksheet
> * * * z = 1
>
> * * * Set ws = Worksheets("Sheet1")
> * * * lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
> * * * For Each cell In ws.Range("A1:A" & lastrow)
> * * * * * * For i = 1 To Len(cell.Value)
> * * * * * * * * * numval = Mid(cell.Value, i, 1)
> * * * * * * * * * For j = 1 To numval
> * * * * * * * * * * * * ws.Cells(z, "B").Value = numval
> * * * * * * * * * * * * z = z + 1
> * * * * * * * * * Next
> * * * * * * Next
> * * * Next
> End Sub
>
> --
>
> Gary
>
> <johan.geor...@gmail.com> wrote in message
>
> news:50c594ce-1f63-41f0-a55e-(E-Mail Removed)...
>
> > Thx. It is not exactly what I am looking for but I can use it as a
> > good basis. (I actually don't need to create new columns for each
> > value, everything should be listed in one column)
>
> > On 11 juil, 13:48, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> >> i forgot to ask what you wanted to do if a number had a zero in it.
>
> >> --
>
> >> Gary
>
> >> <johan.geor...@gmail.com> wrote in message
>
> >>news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
>
> >> > Hi all,
>
> >> > I have a small but anyway very interesting problem with Excel.
> >> > I am actually starting from a table with some values inside. For
> >> > example:
> >> > 4
> >> > 2
> >> > 5
>
> >> > I am looking for a formula which would give me a new table like this:
> >> > 4
> >> > 4
> >> > 4
> >> > 4
> >> > 2
> >> > 2
> >> > 5
> >> > 5
> >> > 5
> >> > 5
> >> > 5
>
> >> > I tried quite a few things, especially with the offset function, but
> >> > couldn't set it. Your help would be greatly appreciated!
> >> > Of course, I can't do it manually as my numbers look like 127,221 and
> >> > not 4 or 5.
>
> >> > Thank you,
> >> > Johan

As long as I spent the time to develop it, here is another way...

Sub ExpandColumnByValues()
Dim X As Long
Dim Z As Long
Dim Index As Long
Dim LastRow As Long
Dim Total As Long
Dim Contents() As Long
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Total = Application.WorksheetFunction.Sum(.Range("A1:A" & LastRow))
If Total > Rows.Count Then
MsgBox "Error - You will go past the end of the worksheet!"
Exit Sub
End If
ReDim Contents(1 To Total)
For X = 1 To LastRow
For Z = 1 To .Cells(X, "A").Value
Index = Index + 1
Contents(Index) = .Cells(X, "A").Value
Next
Next
For X = 1 To Total
.Cells(X, "A").Value = Contents(X)
Next
End With
End Sub

Rick


<(E-Mail Removed)> wrote in message
news:795dca92-3ae1-43f9-a2fb-(E-Mail Removed)...
Rick, I have only one column to create.
Nigel, I am actually using excel 2003. It is indeed possible to run
out of rows with the kind of result I expect, although in my case the
final table is around 30,000 rows.

Gary, thx for your help. I finally succeeded to get what I was looking
for.

On Jul 11, 3:00 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> you can change the column letter to what you want
>
> Sub test()
> Dim i As Long
> Dim j As Long
> Dim lastrow As Long
> Dim cell As Range
> Dim numval As Long
> Dim z As Long
> Dim ws As Worksheet
> z = 1
>
> Set ws = Worksheets("Sheet1")
> lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
> For Each cell In ws.Range("A1:A" & lastrow)
> For i = 1 To Len(cell.Value)
> numval = Mid(cell.Value, i, 1)
> For j = 1 To numval
> ws.Cells(z, "B").Value = numval
> z = z + 1
> Next
> Next
> Next
> End Sub
>
> --
>
> Gary
>
> <johan.geor...@gmail.com> wrote in message
>
> news:50c594ce-1f63-41f0-a55e-(E-Mail Removed)...
>
> > Thx. It is not exactly what I am looking for but I can use it as a
> > good basis. (I actually don't need to create new columns for each
> > value, everything should be listed in one column)
>
> > On 11 juil, 13:48, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> >> i forgot to ask what you wanted to do if a number had a zero in it.
>
> >> --
>
> >> Gary
>
> >> <johan.geor...@gmail.com> wrote in message
>
> >>news:94b10b29-7fba-4cfc-a080-(E-Mail Removed)...
>
> >> > Hi all,
>
> >> > I have a small but anyway very interesting problem with Excel.
> >> > I am actually starting from a table with some values inside. For
> >> > example:
> >> > 4
> >> > 2
> >> > 5
>
> >> > I am looking for a formula which would give me a new table like this:
> >> > 4
> >> > 4
> >> > 4
> >> > 4
> >> > 2
> >> > 2
> >> > 5
> >> > 5
> >> > 5
> >> > 5
> >> > 5
>
> >> > I tried quite a few things, especially with the offset function, but
> >> > couldn't set it. Your help would be greatly appreciated!
> >> > Of course, I can't do it manually as my numbers look like 127,221 and
> >> > not 4 or 5.
>
> >> > Thank you,
> >> > Johan