Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-dimensional array to 2 column list
I have data like this:
.01 .02 .03 .04 .05 .06 .07 .08 .09 1.0 2.0 2.15 2.3 2.7 3.0 3.26 3.40 3.61 3.89 1.1 3.94 1.2 1.3 1.4 1.5 Etc... this is a basic table to figure the greater number (1st column) + the lesser number (the 1st row) ...the find the array value @ A3:C3. I need to convert this a 2-column file such as: 1.01 2.0 1.02 2.15 1.03 2.3 1.04 2.7 1.05 3.0 etc. Hope this is enough info... Basically it is an array for figuring flow of water...if I know how high the creek is flowing, I can figure how many cfs it is passing. So, if I know it's 2.45 ft...then the table would figure xx.xxx cfs. But in order to bring it into another program I have to have it in a 2-column list. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-dimensional array to 2 column list
Try something like this:
With Your current data table on Sheet1, cells A1:J7 Then On Sheet2 A1: 1.01 A2: =+A1+0.01 (copy that formula down as far as you need) B1: =VLOOKUP(INT(A1*10)/10,Sheet1!$A$1:$J$7,MATCH(ROUND(MOD(A1,0.1),2),She et1!$A$1:$J$1,0),0) (copy that formula down as far as you need) Then...copy the new data table range <edit<paste special .....Check: Values .....Click [OK] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "TDS" wrote: I have data like this: .01 .02 .03 .04 .05 .06 .07 .08 .09 1.0 2.0 2.15 2.3 2.7 3.0 3.26 3.40 3.61 3.89 1.1 3.94 1.2 1.3 1.4 1.5 Etc... this is a basic table to figure the greater number (1st column) + the lesser number (the 1st row) ...the find the array value @ A3:C3. I need to convert this a 2-column file such as: 1.01 2.0 1.02 2.15 1.03 2.3 1.04 2.7 1.05 3.0 etc. Hope this is enough info... Basically it is an array for figuring flow of water...if I know how high the creek is flowing, I can figure how many cfs it is passing. So, if I know it's 2.45 ft...then the table would figure xx.xxx cfs. But in order to bring it into another program I have to have it in a 2-column list. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-dimensional array to 2 column list
Here is some VBA to do this for you
things to do first: 1) get your data on a sheet so that there is nothing else, and the sheet looks exactly like you had it in the mail. So cell A1 is empty, the first row has your .01 etc and the first column has the mail numbers, the rest is data in the middle, and there is nothing else on the sheet. 2) create a new blank sheet in the same workbook and call it "ouput" 3) use this code in a module. See here for instructions (regular module) http://www.contextures.com/xlvba01.html 4) go into your first sheet with the data, and do tools, macros and run the one called hello. ***************** Sub hello() Dim x, y, z As Integer x = 2: y = 2: z = 1 Do Until IsEmpty(Cells(x, 1)) Do Until IsEmpty(Cells(1, y)) Sheets("output").Cells(z, 1).Value = Cells(x, 1).Value + Cells(1, y).Value Sheets("output").Cells(z, 2).Value = Cells(x, y).Value z = z + 1 y = y + 1 Loop y = 2 x = x + 1 Loop End Sub ************ -- Allllen "TDS" wrote: I have data like this: .01 .02 .03 .04 .05 .06 .07 .08 .09 1.0 2.0 2.15 2.3 2.7 3.0 3.26 3.40 3.61 3.89 1.1 3.94 1.2 1.3 1.4 1.5 Etc... this is a basic table to figure the greater number (1st column) + the lesser number (the 1st row) ...the find the array value @ A3:C3. I need to convert this a 2-column file such as: 1.01 2.0 1.02 2.15 1.03 2.3 1.04 2.7 1.05 3.0 etc. Hope this is enough info... Basically it is an array for figuring flow of water...if I know how high the creek is flowing, I can figure how many cfs it is passing. So, if I know it's 2.45 ft...then the table would figure xx.xxx cfs. But in order to bring it into another program I have to have it in a 2-column list. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
2-dimensional array to 2 column list
Hello, Allllen!
You wrote on Tue, 31 Oct 2006 09:16:02 -0800: A things to do first: A 1) get your data on a sheet so that there is nothing else, A and the sheet looks exactly like you had it in the mail. So A cell A1 is empty, the first row has your .01 etc and the A first column has the mail numbers, the rest is data in the A middle, and there is nothing else on the sheet. A 2) create a new blank sheet in the same workbook and call it A "ouput" A 3) use this code in a module. See here for instructions A (regular module) http://www.contextures.com/xlvba01.html A 4) go into your first sheet with the data, and do tools, A macros and run the one called hello. A ***************** A Sub hello() A Dim x, y, z As Integer A x = 2: y = 2: z = 1 A Do Until IsEmpty(Cells(x, 1)) A Do Until IsEmpty(Cells(1, y)) A Sheets("output").Cells(z, 1).Value = Cells(x, A 1).Value + Cells(1, y).Value A Sheets("output").Cells(z, 2).Value = Cells(x, A y).Value A z = z + 1 A y = y + 1 A Loop A y = 2 A x = x + 1 A Loop A End Sub A ************ A -- A Allllen A "TDS" wrote: ?? I have data like this: ?? .01 .02 .03 .04 .05 .06 .07 .08 .09 ?? 1.0 2.0 2.15 2.3 2.7 3.0 3.26 3.40 3.61 3.89 ?? 1.1 3.94 ?? 1.2 ?? 1.3 ?? 1.4 ?? 1.5 ?? Etc... this is a basic table to figure the greater number ?? (1st column) + the lesser number (the 1st row) ...the find ?? the array value @ A3:C3. I need to convert this a 2-column ?? file such as: ?? ?? 1.01 2.0 ?? 1.02 2.15 ?? 1.03 2.3 ?? 1.04 2.7 ?? 1.05 3.0 ?? etc. ?? ?? Hope this is enough info... ?? ?? Basically it is an array for figuring flow of water...if I ?? know how high the creek is flowing, I can figure how many ?? cfs it is passing. So, if I know it's 2.45 ft...then the ?? table would figure xx.xxx cfs. But in order to bring it ?? into another program I have to have it in a 2-column list. ?? ?? Thanks! The very useful utility PureText will deal with this in a fashion. Select and copy the columns Run PureText Paste into say Word You then have tab separated columns. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
Formula to search for given term, and if not found in column to add it to list | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions |