How to extract data from an array

J

jmccaski

I have an array with over 21,000 rows. I need to analyze this but have
not been able to get it out in a useful format yet. The data looks
like this:

VarName TimeString VarValue
Pos 27.01.2011 12:49:35 32
Pres 27.01.2011 12:49:35 -2
Setpt 27.01.2011 12:49:35 100
Gain 27.01.2011 12:49:35 1
Int 27.01.2011 12:49:35 10
Pos 27.01.2011 12:49:36 32
Pres 27.01.2011 12:49:36 -2
Setpt 27.01.2011 12:49:36 100
Pos 27.01.2011 12:49:37 32
Pres 27.01.2011 12:49:37 9
Setpt 27.01.2011 12:49:37 100
Pos 27.01.2011 12:49:38 32
Pres 27.01.2011 12:49:38 9
Setpt 27.01.2011 12:49:38 100
Pos 27.01.2011 12:49:39 32
Pres 27.01.2011 12:49:39 24
Setpt 27.01.2011 12:49:39 100
Pos 27.01.2011 12:49:40 32
Pres 27.01.2011 12:49:40 24
Setpt 27.01.2011 12:49:40 100
Gain 27.01.2011 12:49:40 1
Int 27.01.2011 12:49:40 10

I need to extract it into a format something like this in order to
chart it:

TimeString Pos Press Setpt Gain Int
27.01.2011 12:49:35 32 -2 100 1 10
27.01.2011 12:49:36 32 -2 100
27.01.2011 12:49:37 32 9 100
27.01.2011 12:49:38 32 9 100
27.01.2011 12:49:39 32 24 100
27.01.2011 12:49:40 32 24 100 1 10

Any help would be appreciated. I've burned up a couple of days so far
with little luck.
 
G

GS

jmccaski brought next idea :
I have an array with over 21,000 rows. I need to analyze this but have
not been able to get it out in a useful format yet. The data looks
like this:

VarName TimeString VarValue
Pos 27.01.2011 12:49:35 32
Pres 27.01.2011 12:49:35 -2
Setpt 27.01.2011 12:49:35 100
Gain 27.01.2011 12:49:35 1
Int 27.01.2011 12:49:35 10
Pos 27.01.2011 12:49:36 32
Pres 27.01.2011 12:49:36 -2
Setpt 27.01.2011 12:49:36 100
Pos 27.01.2011 12:49:37 32
Pres 27.01.2011 12:49:37 9
Setpt 27.01.2011 12:49:37 100
Pos 27.01.2011 12:49:38 32
Pres 27.01.2011 12:49:38 9
Setpt 27.01.2011 12:49:38 100
Pos 27.01.2011 12:49:39 32
Pres 27.01.2011 12:49:39 24
Setpt 27.01.2011 12:49:39 100
Pos 27.01.2011 12:49:40 32
Pres 27.01.2011 12:49:40 24
Setpt 27.01.2011 12:49:40 100
Gain 27.01.2011 12:49:40 1
Int 27.01.2011 12:49:40 10

I need to extract it into a format something like this in order to
chart it:

TimeString Pos Press Setpt Gain Int
27.01.2011 12:49:35 32 -2 100 1 10
27.01.2011 12:49:36 32 -2 100
27.01.2011 12:49:37 32 9 100
27.01.2011 12:49:38 32 9 100
27.01.2011 12:49:39 32 24 100
27.01.2011 12:49:40 32 24 100 1 10

Any help would be appreciated. I've burned up a couple of days so far
with little luck.
Click to expand...

What's the structure of the array?

Is it something like:

myArray(0,0)=[VarName]
myArray(0,1)=TimeString
myArray(0,2)=VarValue

so that:

myArray(0,0) = "Pos"
myArray(1,0) = "Pres"
myArray(2,0) = "Setpt"
myArray(3,0) = "Gain"
myArray(4,0) = "Int"
myArray(5,0) = "Pos"
...and so on

If so then...

To put the values into ColsA:C

For n = LBound(myArray) To UBound(myArray)
Cells(n + 1, 1) = myArray(n,0)
Cells(n + 1, 2) = myArray(n,1)
Cells(n + 1, 3) = myArray(n,2)
Next
 
J

jmccaski

jmccaski brought next idea :


I have an array with over 21,000 rows. I need to analyze this but have
not been able to get it out in a useful format yet. The data looks
like this:
Click to expand...
VarName    TimeString      VarValue
Pos        27.01.2011 12:49:35     32
Pres       27.01.2011 12:49:35     -2
Setpt      27.01.2011 12:49:35     100
Gain       27.01.2011 12:49:35     1
Int        27.01.2011 12:49:35     10
Pos        27.01.2011 12:49:36     32
Pres       27.01.2011 12:49:36     -2
Setpt      27.01.2011 12:49:36     100
Pos        27.01.2011 12:49:37     32
Pres       27.01.2011 12:49:37     9
Setpt      27.01.2011 12:49:37     100
Pos        27.01.2011 12:49:38     32
Pres       27.01.2011 12:49:38     9
Setpt      27.01.2011 12:49:38     100
Pos        27.01.2011 12:49:39     32
Pres       27.01.2011 12:49:39     24
Setpt      27.01.2011 12:49:39     100
Pos        27.01.2011 12:49:40     32
Pres       27.01.2011 12:49:40     24
Setpt      27.01.2011 12:49:40     100
Gain       27.01.2011 12:49:40     1
Int        27.01.2011 12:49:40     10
Click to expand...
I need to extract it into a format something like this in order to
chart it:
Click to expand...
TimeString                Pos  Press Setpt Gain Int
27.01.2011 12:49:35        32      -2      100          1    10
27.01.2011 12:49:36        32      -2      100
27.01.2011 12:49:37        32      9       100
27.01.2011 12:49:38        32      9       100
27.01.2011 12:49:39        32      24      100
27.01.2011 12:49:40        32      24      100          1    10
Click to expand...
Any help would be appreciated. I've burned up a couple of days so far
with little luck.
Click to expand...

What's the structure of the array?

Is it something like:

  myArray(0,0)=[VarName]
  myArray(0,1)=TimeString
  myArray(0,2)=VarValue

so that:

  myArray(0,0) = "Pos"
  myArray(1,0) = "Pres"
  myArray(2,0) = "Setpt"
  myArray(3,0) = "Gain"
  myArray(4,0) = "Int"
  myArray(5,0) = "Pos"
  ...and so on

If so then...

To put the values into ColsA:C

  For n = LBound(myArray) To UBound(myArray)
    Cells(n + 1, 1) = myArray(n,0)
    Cells(n + 1, 2) = myArray(n,1)
    Cells(n + 1, 3) = myArray(n,2)
  Next

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

I misspoke calling it an array... it is just three columns of data,
VarName, TimeString and VarValue. I need to extract all the variables
for each time step.
 
P

Paul Robinson

Hi
This looks perfect for a pivot table.
1. Select your data
2. go to Data, pivot table
3. in Excel 2000 fill in the data range & after clicking Next you will
see a button for Layout. Click this and drag Time String heading to
the Row area, Var Name to the column area and Value to the data area.
This will give you a count of each TimeString/Var Name pair. Double
click each count and you will get a separate table for it.
Not quite what you asked for but may be useful.
regards
Paul
 
G

GS

jmccaski brought next idea :


I have an array with over 21,000 rows. I need to analyze this but have
not been able to get it out in a useful format yet. The data looks
like this:
Click to expand...
VarName    TimeString      VarValue
Pos        27.01.2011 12:49:35     32
Pres       27.01.2011 12:49:35     -2
Setpt      27.01.2011 12:49:35     100
Gain       27.01.2011 12:49:35     1
Int        27.01.2011 12:49:35     10
Pos        27.01.2011 12:49:36     32
Pres       27.01.2011 12:49:36     -2
Setpt      27.01.2011 12:49:36     100
Pos        27.01.2011 12:49:37     32
Pres       27.01.2011 12:49:37     9
Setpt      27.01.2011 12:49:37     100
Pos        27.01.2011 12:49:38     32
Pres       27.01.2011 12:49:38     9
Setpt      27.01.2011 12:49:38     100
Pos        27.01.2011 12:49:39     32
Pres       27.01.2011 12:49:39     24
Setpt      27.01.2011 12:49:39     100
Pos        27.01.2011 12:49:40     32
Pres       27.01.2011 12:49:40     24
Setpt      27.01.2011 12:49:40     100
Gain       27.01.2011 12:49:40     1
Int        27.01.2011 12:49:40     10
I need to extract it into a format something like this in order to
chart it:
Click to expand...
TimeString                Pos  Press Setpt Gain Int
27.01.2011 12:49:35        32      -2      100          1    10
27.01.2011 12:49:36        32      -2      100
27.01.2011 12:49:37        32      9       100
27.01.2011 12:49:38        32      9       100
27.01.2011 12:49:39        32      24      100
27.01.2011 12:49:40        32      24      100          1    10
Any help would be appreciated. I've burned up a couple of days so far
with little luck.
Click to expand...

What's the structure of the array?

Is it something like:

  myArray(0,0)=[VarName]
  myArray(0,1)=TimeString
  myArray(0,2)=VarValue

so that:

  myArray(0,0) = "Pos"
  myArray(1,0) = "Pres"
  myArray(2,0) = "Setpt"
  myArray(3,0) = "Gain"
  myArray(4,0) = "Int"
  myArray(5,0) = "Pos"
  ...and so on

If so then...

To put the values into ColsA:C

  For n = LBound(myArray) To UBound(myArray)
    Cells(n + 1, 1) = myArray(n,0)
    Cells(n + 1, 2) = myArray(n,1)
    Cells(n + 1, 3) = myArray(n,2)
  Next

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

I misspoke calling it an array... it is just three columns of data,
VarName, TimeString and VarValue. I need to extract all the variables
for each time step.
Click to expand...

Extract how, and to where?

You could use AutoFilter to filter on Varname or TimeString Cols to
view by either. Otherwise, maybe Paul's suggestion might be a viable
alternative.
 
J

jmccaski

Hi
This looks perfect for a pivot table.
1. Select your data
2. go to Data, pivot table
3. in Excel 2000 fill in the data range & after clicking Next you will
see a button for Layout. Click this and drag Time String heading to
the Row area, Var Name to the column area and Value to the data area.
This will give you a count of each TimeString/Var Name pair. Double
click each count and you will get a separate table for it.
Not quite what you asked for but may be useful.
regards
Paul
Click to expand...

The pivot table worked perfectly. I had never used one before. I did
need to "copy" the table into another sheet as data in order to
manipulate it, but having done that I was able to chart everything.

Thanks!!!
 
J

jmccaski

jmccaski brought next idea :
I have an array with over 21,000 rows. I need to analyze this but have
not been able to get it out in a useful format yet. The data looks
like this:
VarName    TimeString      VarValue
Pos        27.01.2011 12:49:35     32
Pres       27.01.2011 12:49:35     -2
Setpt      27.01.2011 12:49:35     100
Gain       27.01.2011 12:49:35     1
Int        27.01.2011 12:49:35     10
Pos        27.01.2011 12:49:36     32
Pres       27.01.2011 12:49:36     -2
Setpt      27.01.2011 12:49:36     100
Pos        27.01.2011 12:49:37     32
Pres       27.01.2011 12:49:37     9
Setpt      27.01.2011 12:49:37     100
Pos        27.01.2011 12:49:38     32
Pres       27.01.2011 12:49:38     9
Setpt      27.01.2011 12:49:38     100
Pos        27.01.2011 12:49:39     32
Pres       27.01.2011 12:49:39     24
Setpt      27.01.2011 12:49:39     100
Pos        27.01.2011 12:49:40     32
Pres       27.01.2011 12:49:40     24
Setpt      27.01.2011 12:49:40     100
Gain       27.01.2011 12:49:40     1
Int        27.01.2011 12:49:40     10
I need to extract it into a format something like this in order to
chart it:
TimeString                Pos  Press Setpt Gain Int
27.01.2011 12:49:35        32      -2      100          1    10
27.01.2011 12:49:36        32      -2      100
27.01.2011 12:49:37        32      9       100
27.01.2011 12:49:38        32      9       100
27.01.2011 12:49:39        32      24      100
27.01.2011 12:49:40        32      24      100          1    10
Any help would be appreciated. I've burned up a couple of days so far
with little luck.
What's the structure of the array?
Is it something like:
  myArray(0,0)=[VarName]
  myArray(0,1)=TimeString
  myArray(0,2)=VarValue
so that:
  myArray(0,0) = "Pos"
  myArray(1,0) = "Pres"
  myArray(2,0) = "Setpt"
  myArray(3,0) = "Gain"
  myArray(4,0) = "Int"
  myArray(5,0) = "Pos"
  ...and so on
If so then...
To put the values into ColsA:C
  For n = LBound(myArray) To UBound(myArray)
    Cells(n + 1, 1) = myArray(n,0)
    Cells(n + 1, 2) = myArray(n,1)
    Cells(n + 1, 3) = myArray(n,2)
  Next
Click to expand...
Click to expand...
I misspoke calling it an array... it is just three columns of data,
VarName, TimeString and VarValue. I need to extract all the variables
for each time step.
Click to expand...

Extract how, and to where?

You could use AutoFilter to filter on Varname or TimeString Cols to
view by either. Otherwise, maybe Paul's suggestion might be a viable
alternative.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thanks for the reply. The pivot table did the job.

Regards
 
G

GS

Hi Paul,

Just want to say thanks for the clear, concise instruction. I too have
never used Pivot Table and so I decided to try your suggestion using
the OP's data. I was in xl10 and so the wizard is slightly different
but you instructions were clear enough to follow and so was no problem.

Works beautifully!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number Match Formula? 7
Record Click on Error Indicator 2
Solver 0
For Loop 9
counting values as they turn up in a column 4
need help finding most common number and so on 2
get a list of sequence number that meet 3 criteria 0
Looping Macro to scrap multiple pages in Mainframe 2

Top