How to Extract Min-Max Data Using Excel or Access?? 1

[3dmap1]

I have a text file with 42,600 rows of data and 16 columns. This file shows information for 270 data tapes and approximately 100 to 200 id #'s on each tape. I need to sort by tape # and extract the min. and max. id # for each tape but I can't figure out how to do it in Excel or Access. I hoped I could use an "IF" statement and then use the "then" or "min" statement to get the result, then do the same with the "max" function, ex: if(a2:a42600=1, and add a "then" statement to return the min of d2:d42600.

Does anyone have any suggestions?

 

[AnotherHiggins]

This is simple in Excel (or Access for that matter, but seeing how Access has its own forum - forum707 - I'll discuss an Excel solution here).

If your data is normalized, then using a pivot table this will take you about 10 seconds. One question, though.... What headers do you have on those 16 columns? I have a bad feeling your data is NOT normalized.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[3dmap1]

Below are the column headers, I put "[ ]" around them to separate them. I am wanting to look at "Reel #1" and take each reel (1 to 270) and get the high and low number on "EP Concat". As far as normalized, it is just data extracted from about 109 files that were combined, each file represents one day so 109 days.

[ID] [Date] [Line] [Shot ID] [EP ID] [Station] [Reel #1] [File #1] [Reel #2] [File #2] [EP Line] [EP Sta] [Top Left] [Bottom Right] [Channels] [Uphole] [Local Time] [EP Concat]

 

[KenWright]

Select all your data, then do Data / Pivot table and Chart report, drag 'Reel #1' into the ROW fields on the left, and then drag 'EP Concat' into the DATA field in the middle. Now drag 'EP Concat' once again into the DATA field and you should see it there twice, with one saying EP Concat and the second saying EP Concat2.

Right click on any of the numbers in the EP Concat section and choose Field Settings / Summarise by / Min

Right click on any of the numbers in the EP Concat2 section and choose Field Settings / Summarise by / Max

Now finally left click and hold what will likely be cell B3 (Grey box saying Data) and drag it to the COLUMN fields, which is likely to be cell C3, or if your pivot table is in a different place then it will be one cell to the right of the Grey box marked Data. This will give you Field number down the left and Min and Max across the top.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[3dmap1]

Sounds great, I tried this on a small part and it worked great . . .THANKS A MILLION. . .I guess I will have to break it up into groups since it says its limit is 8000 ?I think it said rows? and I have 42,264 rows. I have to get up for work in 5 hours so I will work on the rest of it there. Thanks again

 

[KenWright]

Noooooooooo - Limit for items diplayed in the Pivot Table Report only is 8000 items. Just throw the pivot table against your whole data set. I occasionally use it with hundreds of thousands of rows because I'll sometimes use an Access table as the source.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[3dmap1]

Excellent, It works fantastic!! That will also be very helpful in the future. Thank's again Ken.

 

[KenWright]

You're very welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------