Sample matching

[seth]

I have a dataset that contains samples from different treatments for
various sample points at different sample dates. There are 4
treatments, 36-40 sample points, and 4 sample dates. What I would
like to do is group the samples by treatment, sample point, and sample
date and then find the range of values for each sample point on that
sample date. From this range of values I then need to identify the
top twenty percent and assign those samples to class "a", the second
twenty percent and assign to class "b", the third twenty percent
assigned to class "c", and the last forty percent assigned to class
"d". The problem that I am running into is that each sample point has
a different number of samples so I cannot use a set number of rows to
define the range of cells for each sample point. Since I am not
fluent in VB, I am struggling with this issue and would appreciate any
help.

To recap: I need to be able to write a macro that can match the 1st 3
columns (treatment, sample point, sample date) and then identify the
range of values for the sample point and assign categories based on
the above criteria for each sample.

 

[Tom Ogilvy]

Look at the Autofilter under Data=>filter=>Autofilter

You can filter on the first three columns to see the records meeting the
criteria.


You can record a macro while you do this to get the basic code

You can use subtotal with a filter and it designate the first argument to
show Min and for a second subtotal, Max

This will give you your range.

Another approach might be to use an array formula in the next available
column to discover the min/max and calculate the current records position in
the data. You may want to use several columns to reduced the complexity of
the calculation and to avoid having to repeat sections of you formula

first column Min
second column Max
third column range
4th column cutoff for 1st group
5th column cuttooff for 2nd group
6th column cutoff for 3rd group
7th column cutoff for 4th group
8th column use the 5th to 6th columns to calculate assignment.

 

[Bernie Deitrick]

seth,

Seems like a job for Pivot Tables. You may need to modify your data table
to allow for their use: post a SMALL sample of data in a message and we'll
all take a look.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

I don't see a pivot table assigning records to categories established as
percentages of the range of the values. may be able to use several to
gather the basic information needed to set up the categories.

 

[Seth Tiffner]

Here is a sample of the data set that I am working with:
Columns 1,2,3 are the columns that I am trying to group by treatment,
sample point, and sample date and then column 6 is the column that I
need to find the range of values for each sample point on that sample
date. Column 6 is also the column that I am needing to identify the top
twenty percent and assign those samples to class "a", the second
twenty percent and assign to class "b", the third twenty percent
assigned to class "c", and the last forty percent assigned to class "d".

R2TB-CLM 26 5/2000 340 PITA 15
R2TB-CLM 26 5/2000 341 PITA 20
R2TB-CLM 26 5/2000 342 QUVE 9
R2TB-CLM 26 5/2000 358 ULAL 15
R2TB-CLM 26 5/2000 359 ULAL 14
R2TB-CLM 26 5/2000 360 PITA 17
R2TB-CLM 26 5/2000 361 PITA 11
R2TB-CLM 26 5/2000 362 PITA 15
R2TB-CLM 26 5/2000 363 PITA 15
R2TB-CLM 26 5/2000 364 PITA 15
R2TB-CLM 26 5/2000 365 PITA 11
R2TB-CLM 26 5/2000 366 PIEC2 21
R2TB-CLM 26 5/2000 367 PIEC2 21
R2TB-CLM 26 5/2000 368 PIVI2 12
R2TB-CLM 26 5/2000 369 LIST2 9
R2TB-CLM 26 5/2000 370 PIVI2 20
R2TB-CLM 26 5/2000 371 PIEC2 23
R2TB-CLM 26 5/2000 372 PRSE2 12
R2TB-CLM 26 5/2000 373 LIST2 11
R2TB-CLM 26 5/2000 374 LIST2 15
R2TB-CLM 26 5/2000 375 PIEC2 23
R2TB-CLM 26 5/2000 376 LIST2 8
R2TB-CLM 26 5/2000 473 PITA 21
R2TB-CLM 26 5/2000 475 PRSE2 9
R2TB-CLM 26 5/2000 476 LIST2 9
R2TB-CLM 26 5/2000 477 PIEC2 18
R2TB-CLM 26 5/2000 484 LIST2 12
R2TB-CLM 26 5/2000 485 ULAL 12
R2TB-CLM 26 5/2000 486 LIST2 6
R2TB-CLM 26 5/2000 487 PITA 9
R2TB-CLM 26 5/2000 488 PRSE2 8
R2TB-CLM 26 5/2000 489 PIEC2 9
R2TB-CLM 26 5/2000 490 PITA 6
R2TB-CLM 26 5/2000 491 PITA 9
R2TB-CLM 26 5/2000 492 PITA 9
R2TB-CLM 26 5/2000 493 PIEC2 15
R2TB-CLM 26 5/2000 494 PRSE2 8
R2TB-CLM 26 5/2000 495 PRSE2 8
R2TB-CLM 26 5/2000 497 PIEC2 15
R2TB-CLM 26 5/2000 498 PITA 11
R2TB-CLM 26 5/2000 499 PIEC2 20
R2TB-CLM 26 5/2000 500 PIEC2 20
R2TB-CLM 30 5/2000 201 PIEC2 20
R2TB-CLM 30 5/2000 202 LIST2 9
R2TB-CLM 30 5/2000 203 LIST2 15
R2TB-CLM 30 5/2000 204 LIST2 12
R2TB-CLM 30 5/2000 205 PIEC2 20
R2TB-CLM 30 5/2000 206 PITA 18
R2TB-CLM 30 5/2000 207 PIVI2 11
R2TB-CLM 30 5/2000 208 LIST2 9
R2TB-CLM 30 5/2000 209 LIST2 14
R2TB-CLM 30 5/2000 210 PIEC2 21
R2TB-CLM 30 5/2000 211 PIVI2 12
R2TB-CLM 30 5/2000 212 LIST2 11
R2TB-CLM 30 5/2000 213 PIEC2 11
R2TB-CLM 30 5/2000 214 PITA 23
R2TB-CLM 30 5/2000 215 LIST2 11
R2TB-CLM 30 5/2000 216 LIST2 11
R2TB-CLM 30 5/2000 225 LIST2 14
R2TB-CLM 30 5/2000 226 PITA 15
R2TB-CLM 30 5/2000 227 PITA 11
R2TB-CLM 30 5/2000 228 COFL2 6
R2TB-CLM 30 5/2000 229 PIEC2 14
R2TB-CLM 30 5/2000 230 PITA 11
R2TB-CLM 30 5/2000 231 PIEC2 15
R2TB-CLM 30 5/2000 232 PIVI2 15
R2TB-CLM 30 5/2000 233 PITA 12
R2TB-CLM 30 5/2000 234 LIST2 23
R2TB-CLM 30 5/2000 235 ACRU 11
R2TB-CLM 30 5/2000 236 PIVI2 17
R2TB-CLM 30 5/2000 237 PIEC2 9
R2TB-CLM 30 5/2000 238 PIEC2 14
R2TB-CLM 30 5/2000 239 PIEC2 12
R2TB-CLM 30 5/2000 240 PIEC2 11
R2TB-CLM 30 5/2000 241 PIEC2 15
R2TB-CLM 30 5/2000 242 PIEC2 17
R2TB-CLM 30 5/2000 243 PIVI2 18
R2TB-CLM 30 5/2000 260 PIVI2 11
R2TB-CLM 30 5/2000 261 PIVI2 18
R2TB-CLM 30 5/2000 263 LIST2 9
R2TB-CLM 30 5/2000 264 PIVI2 15
R2TB-CLM 30 5/2000 265 PITA 12
R2TB-CLM 30 5/2000 266 LIST2 8
R2TB-CLM 30 5/2000 267 PITA 12
R2TB-CLM 30 5/2000 268 PITA 21
R2TB-CLM 30 5/2000 269 PIEC2 21
R2TB-CLM 30 5/2000 270 PIVI2 8
R2TB-CLM 30 5/2000 271 PIEC2 12
R2TB-CLM 30 5/2000 272 PIVI2 12
R2TB-CLM 30 5/2000 273 PIVI2 12
R2TB-CLM 30 5/2000 274 PITA 23
R2TB-CLM 30 5/2000 275 PITA 15
R2TB-CLM 30 5/2000 276 JUVI 9
R2TB-CLM 30 5/2000 277 PIVI2 18
R2TB-CLM 30 5/2000 278 PIVI2 11

 

[seth]

seth,

Seems like a job for Pivot Tables. You may need to modify your data table
to allow for their use: post a SMALL sample of data in a message and we'll
all take a look.

HTH,
Bernie
MS Excel MVP

Here is a sample of the data set that I am working with:
Columns 1,2,3 are the columns that I am trying to group by treatment,
sample point, and sample date and then column 6 is the column that I
need to find the range of values for each sample point on that sample
date. Column 6 is also the column that I am needing to identify the top
twenty percent and assign those samples to class "a", the second
twenty percent and assign to class "b", the third twenty percent
assigned to class "c", and the last forty percent assigned to class "d".

R2TB-CLM 26 5/2000 340 PITA 15
R2TB-CLM 26 5/2000 341 PITA 20
R2TB-CLM 26 5/2000 342 QUVE 9
R2TB-CLM 26 5/2000 358 ULAL 15
R2TB-CLM 26 5/2000 359 ULAL 14
R2TB-CLM 26 5/2000 360 PITA 17
R2TB-CLM 26 5/2000 361 PITA 11
R2TB-CLM 26 5/2000 362 PITA 15
R2TB-CLM 26 5/2000 363 PITA 15
R2TB-CLM 26 5/2000 364 PITA 15
R2TB-CLM 26 5/2000 365 PITA 11
R2TB-CLM 26 5/2000 366 PIEC2 21
R2TB-CLM 26 5/2000 367 PIEC2 21
R2TB-CLM 26 5/2000 368 PIVI2 12
R2TB-CLM 26 5/2000 369 LIST2 9
R2TB-CLM 26 5/2000 370 PIVI2 20
R2TB-CLM 26 5/2000 371 PIEC2 23
R2TB-CLM 26 5/2000 372 PRSE2 12
R2TB-CLM 26 5/2000 373 LIST2 11
R2TB-CLM 26 5/2000 374 LIST2 15
R2TB-CLM 26 5/2000 375 PIEC2 23
R2TB-CLM 26 5/2000 376 LIST2 8
R2TB-CLM 26 5/2000 473 PITA 21
R2TB-CLM 26 5/2000 475 PRSE2 9
R2TB-CLM 26 5/2000 476 LIST2 9
R2TB-CLM 26 5/2000 477 PIEC2 18
R2TB-CLM 26 5/2000 484 LIST2 12
R2TB-CLM 26 5/2000 485 ULAL 12
R2TB-CLM 26 5/2000 486 LIST2 6
R2TB-CLM 26 5/2000 487 PITA 9
R2TB-CLM 26 5/2000 488 PRSE2 8
R2TB-CLM 26 5/2000 489 PIEC2 9
R2TB-CLM 26 5/2000 490 PITA 6
R2TB-CLM 26 5/2000 491 PITA 9
R2TB-CLM 26 5/2000 492 PITA 9
R2TB-CLM 26 5/2000 493 PIEC2 15
R2TB-CLM 26 5/2000 494 PRSE2 8
R2TB-CLM 26 5/2000 495 PRSE2 8
R2TB-CLM 26 5/2000 497 PIEC2 15
R2TB-CLM 26 5/2000 498 PITA 11
R2TB-CLM 26 5/2000 499 PIEC2 20
R2TB-CLM 26 5/2000 500 PIEC2 20
R2TB-CLM 30 5/2000 201 PIEC2 20
R2TB-CLM 30 5/2000 202 LIST2 9
R2TB-CLM 30 5/2000 203 LIST2 15
R2TB-CLM 30 5/2000 204 LIST2 12
R2TB-CLM 30 5/2000 205 PIEC2 20
R2TB-CLM 30 5/2000 206 PITA 18
R2TB-CLM 30 5/2000 207 PIVI2 11
R2TB-CLM 30 5/2000 208 LIST2 9
R2TB-CLM 30 5/2000 209 LIST2 14
R2TB-CLM 30 5/2000 210 PIEC2 21
R2TB-CLM 30 5/2000 211 PIVI2 12
R2TB-CLM 30 5/2000 212 LIST2 11
R2TB-CLM 30 5/2000 213 PIEC2 11
R2TB-CLM 30 5/2000 214 PITA 23
R2TB-CLM 30 5/2000 215 LIST2 11
R2TB-CLM 30 5/2000 216 LIST2 11
R2TB-CLM 30 5/2000 225 LIST2 14
R2TB-CLM 30 5/2000 226 PITA 15
R2TB-CLM 30 5/2000 227 PITA 11
R2TB-CLM 30 5/2000 228 COFL2 6
R2TB-CLM 30 5/2000 229 PIEC2 14
R2TB-CLM 30 5/2000 230 PITA 11
R2TB-CLM 30 5/2000 231 PIEC2 15
R2TB-CLM 30 5/2000 232 PIVI2 15
R2TB-CLM 30 5/2000 233 PITA 12
R2TB-CLM 30 5/2000 234 LIST2 23
R2TB-CLM 30 5/2000 235 ACRU 11
R2TB-CLM 30 5/2000 236 PIVI2 17
R2TB-CLM 30 5/2000 237 PIEC2 9
R2TB-CLM 30 5/2000 238 PIEC2 14
R2TB-CLM 30 5/2000 239 PIEC2 12
R2TB-CLM 30 5/2000 240 PIEC2 11
R2TB-CLM 30 5/2000 241 PIEC2 15
R2TB-CLM 30 5/2000 242 PIEC2 17
R2TB-CLM 30 5/2000 243 PIVI2 18
R2TB-CLM 30 5/2000 260 PIVI2 11
R2TB-CLM 30 5/2000 261 PIVI2 18
R2TB-CLM 30 5/2000 263 LIST2 9
R2TB-CLM 30 5/2000 264 PIVI2 15
R2TB-CLM 30 5/2000 265 PITA 12
R2TB-CLM 30 5/2000 266 LIST2 8
R2TB-CLM 30 5/2000 267 PITA 12
R2TB-CLM 30 5/2000 268 PITA 21
R2TB-CLM 30 5/2000 269 PIEC2 21
R2TB-CLM 30 5/2000 270 PIVI2 8
R2TB-CLM 30 5/2000 271 PIEC2 12
R2TB-CLM 30 5/2000 272 PIVI2 12
R2TB-CLM 30 5/2000 273 PIVI2 12
R2TB-CLM 30 5/2000 274 PITA 23
R2TB-CLM 30 5/2000 275 PITA 15
R2TB-CLM 30 5/2000 276 JUVI 9
R2TB-CLM 30 5/2000 277 PIVI2 18
R2TB-CLM 30 5/2000 278 PIVI2 11

 

[Bernie Deitrick]

Seth,

Once you have set up your pivot table (without subtotals), you need two more
columns The first uses a formula like
=RANK(D5,$D$5:$D$XXX,FALSE)/COUNT($D$5:$D$XXX)
=RANK(D5,$D$5:$D$XXX,TRUE)/COUNT($D$5:$D$XXX)
Assuming your first data point is in row 5, and the last is in row XXX. The
use of TRUE/FALSE in the RANK function depends on whether a high score is
good or bad. Let's say that formula is in cell F5.

The second formula used is:
=IF(F5<=0.2,"A",IF(F5<=0.4,"B",IF(F5<=0.6,"C","D")))
(though you didn't say what happens to exact values of percentages)

Copy both formula down to match your pivot table, and you're done.

Your data set doesn't include many variations on your key values - only one
treatment, two sample points, and one date. I have modified it to give it
more variability for illustration purposes, and will send you a sample
spreadsheet.

HTH,
Bernie
MS Excel MVP