Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table creation in excel | Excel Worksheet Functions | |||
pivot table creation procedures | Excel Discussion (Misc queries) | |||
Excel summary table creation | Excel Discussion (Misc queries) | |||
Table creation help please | Excel Worksheet Functions | |||
Pivot Table Creation | Excel Programming |