2-dimensional array to 2 column list

[Guest]

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!

 

[Guest]

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),Sheet1!$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

 

[Guest]

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

************

 

[James Silverton]

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