Tips to filter data within a given range across multiple worksheets

**saanabenurwar** · 08-08-2022, 10:11 PM

Hello,
I am Saana.

I am currently using excel to segregate a large amount of tree diameter data into different size classes, this includes data collected over 128 plots and thus has multiple duplicate values for one species.

For example:

I want to divide the trees into 4 specific ranges, the diameter of 10-30cm, 30-50cm, 50-100cm, and 100cm and above, how do I proceed to do that?
Thank you.

**alansidman** · 08-08-2022, 11:22 PM

Sample workbooks make this easier to resolve. Post a sample file that is representative of your actual data. No pictures.

**saanabenurwar** · 08-08-2022, 11:54 PM

Hi,
I am hereby attaching the excel sheet, I want to segregate data based on DBH (diameter) and species in different size bin classes. This includes 4 classes, 3-10cm, 10-25cm, 25-50cm and 50 cm and above.
I am not sure how to divide the data which has repetitive species but different diameters in a tabular form and then further divide them based on size class.
Thanks,
Saana.Copy of MVCA analysis (version 1).xlsx

**alansidman** · 08-09-2022, 10:32 AM

You have 2 DBH columns. Which one should we be using. Or are there two different tables there but set adjacent to each other. Very confusing. Which column(s) are the cm settings. Need you to explain in detail using your example.

**saanabenurwar** · 08-09-2022, 12:00 PM

Apologies,
Both the columns from the first sheet have to be used for DBH, and the corresponding species is next to the column. The data is divided plot-wise based on species found in each plot this is denoted by the VSP ID.
VSP ID for example in the worksheet the plot number VSP ID would be 4 then the species name next to it followed by the DBH in cm.
VSP ID Species DBH VSP ID
8 Acer saccharum ssp. saccharum 25
8 Acer saccharum ssp. saccharum 33.2
8 Acer saccharum ssp. saccharum 41.3
8 Acer saccharum ssp. saccharum 7.5
8 Acer saccharum ssp. saccharum 38.8
8 Acer saccharum ssp. saccharum 9.4
8 Acer saccharum ssp. saccharum 29.6
8 Acer saccharum ssp. saccharum 41.4
8 Acer saccharum ssp. saccharum 18.4
8 Fraxinus americana 11.8
8 Snag 7.5
8 Acer saccharum ssp. saccharum 16.3

4 Fraxinus nigra 19.5
4 Snag 16
4 Tsuga canadensis 6
4 Betula alleghaniensis 40
4 Snag 14
4 Ulmus sp 7.1
4 Acer rubrum 12.1
4 Fraxinus nigra 12.7
4 Fraxinus nigra 19.5
4 Acer rubrum 7.5
4 Ulmus sp 21.1
4 Fraxinus nigra 11.7
4 Fraxinus nigra 26.7

This is an updated copy of the first sheet the two tables contain all the data which needs to be divided into size classes.
Thank you, Saana.
Copy of MVCA analysis (version 1).xlsx

**alansidman** · 08-09-2022, 01:26 PM

Your explanation makes no sense to me. Therefore, I am leaving this thread to someone who has a better understanding due to the technical nature. Good Luck.

**saanabenurwar** · 08-09-2022, 01:50 PM

Thank you for your time.
Saana.

**JeteMc** · 08-13-2022, 04:36 PM

Not sure that I understand the request either, but here is an attempt.
1. Produce one large table (Table16) using the data in the Combo through Plot columns
2. Fill in the blank cells in the Plot column
3. Produce a lookup table for the four classes as modeled in I1:J5
4. Lookup the class of each tree using: =IF([@COMBO]="","",INDEX(I$2:I$5,AGGREGATE(15,6,(ROW(I$2:I$5)-ROW(I$1))/(J$2:J$5>=[@DBH]),1)))
5. Produce a pivot table from Table16
Let us know if you have any questions.