Creation of a table of n values
 

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

Creation of a table of n values
 

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


wrote in message
...
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

Creation of a table of n values
 

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

--


Gary


wrote in message
...
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

Creation of a table of n values
 

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

wrote in message

...

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

Creation of a table of n values
 

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

Creation of a table of n values
 

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




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i forgot to ask what you wanted to do if a number had a zero in it.

--


Gary


wrote in message
...
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

Creation of a table of n values
 

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


wrote in message
...
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

wrote in message

...

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

Creation of a table of n values
 

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

wrote in message

...

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

wrote in message

...

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

Creation of a table of n values
 

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


wrote in message
...
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

wrote in message

...

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

wrote in message

...

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