Data Extraction Question

[Focht3]

I have an Excel spreadsheet that contains soil data from over 4,000 sites over a four year period. Data contains columns for "Site Number", "Location", "Year", "Month", "Depth", "Moisture", etc. I need to extract the moisture data for a given month, location(s) and depth range, average the results, and then plot them on a graph of "average moisture vs time."

Can this be done in Excel, or does it need to be done in Visual Basic? Any sample code that you could share? Guidance?

 

[IRstuff]

Can you turn on Autofilter and select what you need?
TTFN

 

[pmover]

In addition to IRstuff (AutoFilter is a great tool!), use xl's help system to learn how to do the averaging and prepare the chart. i believe you "might" need to use the advanced filter capabilities to accomplish desired results. Again, use the Help system.
-pmover

 

[melone]

Also look at the "group and outline" functionality. You might be able to pre-sort you data into groups, and simply chart all of the data. Then, by carefully placing your "group"'s, you can zip up, or not show the data that you are not concerned about. The chart's you create will only be based on the data that is visible. The nice thing about this approach is that you can easily create charts on different pieces of data, while the formatting / scale / size / etc. all stay constant.

 

[ProjEngKLS]

Set up an array formula.

Suppose "Site Number" is in range A5:A4005, "Location" is in range B5:B4005, "Year" is in range C5:C4005, "Month" is in range D54005, "Depth" is in range E5:E4005, "Moisture" is in range F5:F4005, etc.

Suppose your criterias "Month" is in range D4, "Location" is in B4, "Depth" is in range E4 (you're after a range, so you'll actually have a MinDepth (say in cell E3) and MaxDepth (say in cell E4) for criterias).

You can count how many entries meet the criterias "Location", "Month", and "Depth" with the array formula:

=SUM(IF(B5:B4005=B4,IF(D54005=D4,IF(E5:E4005=E4,1,0))))

You can sum the moistures meeting the three criteria with the array formula:

=SUM(IF(B5:B4005=B4,IF(D54005=D4,IF(E5:E4005=E4,F5:F4005))))

TO ENTER AN ARRAY FORMULA you type the formula into the edit field and then enter it with the combination keystrokes CTRL+SHIFT+ENTER.

Of course the average is just the Sum divided by the Count. I don't know if you can combine these into a single formula. Maybe worth a try...

I noticed you want this to work with a range of depth. I didn't work that part completely out, but you should be able to do this by testing the Depth data against the Depth Criteria. Maybe something like replace &quot;IF(E5:E4005=E4&quot; with &quot;IF(E5:E4005>E3,IF(E5:E4005<E4&quot; will work. You'll have to play with this part a bit.

Good Luck,
ProjEngKLS

 

[ProjEngKLS]

Oh, and using Autofilters and Subtotals should work too. Refer to Excel help reference on subtotals - you can use these to do several things like sum, count, etc and they ignore data that is hidden or filtered out.

Good Luck again,
ProjEngKLS

 

[MikeRL]

Another option may be to use the pivot table option, this will allow you to consolidate the data in categories and will automate the charting as well.