Extract row of data that meets specific criterie

[propman07]

Hi-

I can't seem to figure out how to write a macro to do this. I have a data file that has several columns in it. One of them is a cycle count. I want to find all of the unique cycle counts, then figure out the max/min of the other two columns in the array. Here is an example:

00:00:36 653.79 500.25 26528.00 652.92 76.34 74.79
00:00:38 649.73 500.25 26528.00 482.17 76.28 74.75
00:00:40 649.73 487.03 26529.00 682.52 76.20 74.77
00:00:42 681.47 487.03 26529.00 503.78 76.19 74.74
00:00:44 681.47 470.96 26529.00 609.33 76.27 74.74
[/color red]00:00:46 670.65 470.96 26530.00 556.98 76.22 74.75
00:00:48 670.65 470.96 26530.00 461.52 76.20 74.74
00:00:50 670.65 462.90 26530.00 646.74 76.15 74.78


I would like to be able to extract the cycle number (26529), and the max value from the group in column two that matches the cycle number (681.47) and the minimum number from column three (470.96)

Any thoughts/hints on how to go about this would be appreciated. Thanks.

 

[Loomah]

hi
depending on what you are ultimately trying to achieve you might be able to do (at least some of) this without vba.

however it is decidely unclear what "column two" is and which array you refer to.

one formula to find the max (or min) value in a data range depending on the value in another column is:
=MAX(IF(cycle=A1,data,FALSE))
where cycle is the range containing your cycle numbers and data is the range in which to find the max/min val
A1 is a cell containing the value you are looking for.
the formula is array entered using ctrl+shift+enter
obviously change max to min to get the min values!

taking this all one step further you could recrod yourself doing an advanced filter just on the cycle numbers and copy to a new location then fill this formula in next to your unique list.

however i seriously think you need to give more detail of what you are trying to achieve as well as some indication of what you have already tried.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[propman07]

Hi- Sorry for not clearly defining the problem. I've got a bunch of text data files that are too large for Excel to import. I would like to read each data file, and extract the following information:

From the sample above, I would like to grab one of the unique cycle numbers (i.e. column 4 from my sample). Once I have this number, I would like to look at all rows that have this cycle number, and grab the maximum value from column 2 in my sample, and the minimum value from column 3 in my sample. I would like to take all of this data, and write it as one row in a new excel file. I would like to do this for all of the data in one of the large text files so that when I am done, I have an excel file that has unique counter numbers, along with the corresponding max and min values from columns 2 and 3.

I have tried to filter it in Excel, but I figure that VBA would be the way to go. I have written a few macros in Excel, but I can't think of a way (programatically) to attack the problem.

 

[SkipVought]

Hi,

I would do a Text File Query, using MS Query.

First, you need a Schema.ini file to define the columns in the files you want to query. Such a file looks like this...
[tt]
[test1_010207.txt]
format=FixedLength
col1=Traveler Text Width 7
col2=PartID Text Width 16
col3=SetUp Single Width 6
col4=Run Single Width 6
[resource_data.txt]
format=Delimited(|)
col1=Resource Text
col2=ResDesc Text
Col3=CC Text
Col4=ZZ Text
Col5=COE Text
Col6=OTHER Text
Col7=MORE Text
[/tt]
more on that at

http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Once the schema is completed, you can query using the text driver and get your data by the criteria, returned to an Excel sheet.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[propman07]

Skip-

Thanks for your reply. I'm not sure what MS Query is, so that might not be an option for me, unless it's really easy to learn/implement.

 

[SkipVought]

I gave your the 'key' to get in, in your thread in forum69.

Did you at least TRY?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[propman07]

Skip-

I'm not sure I follow you on this one....and no, I haven't had a chance to try MS Query. I don't have the software installed on my machine, or at least if I do, I can't find it.

The link you gave doesn't make sense to me at all.

 

[SkipVought]

forum68. sorry fat finger sydrome.

What happend when you...

Data > Import external data > New Database query...

That's the 'gateway' to MS Query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[propman07]

Skip-

Got it. I was able to import the data file, now I just have to figure out how to sort through the data to meet my needs.

I'll have to tinker around with it for a while....hopefully, I'll be able to figure it out.

 

[Loomah]

hi
now you're using ms query you can use it to extract the data you need.

sql is not a strong point with me, as will become apparent, but the code below should give an indication

SELECT D,
min(C) as [min],
max(B) as [max]
FROM `Sheet1$` 
group by D

in the above code Sheet1$ is a system table - acts like any other table (or so it seems)
i took your sample data and gave it headings 'A' to 'G' which is where the field names came from.

seems to work but don't know how to combine it with the extracting from a text file....

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

of course, if you've already got your data into excel a pivot table can achieve the results you require

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[propman07]

Loomah-

I haven't used pivot tables before, so I will have to look up how to use them. Thanks for the tip.