grouping data

  • Thread starter Thread starter Carl Hilton
  • Start date Start date
C

Carl Hilton

I have a worksheet with a list of names in one column and a group of
numerical scores in another column.

On a seperate worksheet, I need, a 4 wide x 2 high grid, each grid is
filled with names from the first worksheet, for those that meet a specific
range of scores. Each "grid" is say one cell wide by X cells high with one
person per cell. The ranges are such that no person would meet more that one
range.

If this can be done via worksheet functions great.

Thanks

Carl
 
Venturing some guesses here that
something along these lines could be what you're after ..

Sample construct available at:
http://cjoint.com/?mpl7qrmqYh
Grouping_Data_Carl_Hilton_wks.xls

In Sheet1,
Names are assumed listed in A2:A11, scores in B2:B11
Let's say we want to group the names under 5 categories / cols according to
the scores, viz.:

<=55
55 to <=60
60 to <=65
65 to <=75
75
Click to expand...

Using say, 5 empty cols to the right, cols D to H
Enter the "limits" in D1:G1, viz.: 55, 60, 65, 75
Put a label into H1: ">75"

Put in D2: =IF($B2="","",IF($B2<=D$1,ROW(),""))
Put in E2: =IF(AND($B2>D$1,$B2<=E$1),ROW(),"")
Copy E2 to G2
Put in H2: =IF($B2>G$1,ROW(),"")
Select D2:H2, copy down to H11

In Sheet2,
Copy > paste over the headers from Sheet1's D1:H1 into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Sheet1!$C$2:$C$11,,MATCH(A$1,Sheet1!$D$1:$H$1,0)),R
OWS($A$1:A1))),"",INDEX(Sheet1!$A$2:$A$11,MATCH(SMALL(OFFSET(Sheet1!$C$2:$C$
11,,MATCH(A$1,Sheet1!$D$1:$H$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1!$C$2:$C$11,,
MATCH(A$1,Sheet1!$D$1:$H$1,0)),0)))

Copy A2 across to E2, fill down to E11

Sheet2 will return the names listed in Sheet1's A2:A11 under the correct
cols according to the scores, with all results neatly bunched at the top of
the cols

--
 
Excellent and quick... Thanks Max... Now I just have check out the functions
you used and LEARN!

Thanks again.

Carl
 
Back
Top