grouping data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl Hilton - 15 Dec 2005 01:46 GMT

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

Reply to this Message

Max - 15 Dec 2005 11:03 GMT

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> I have a worksheet with a list of names in one column and a group of
> numerical scores in another column.
[quoted text clipped - 10 lines]
>
> Carl

Reply to this Message

Carl Hilton - 15 Dec 2005 12:56 GMT

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

Thanks again.

Carl

> Venturing some guesses here that
> something along these lines could be what you're after ..
[quoted text clipped - 66 lines]
>>
>> Carl

Reply to this Message

Max - 16 Dec 2005 02:33 GMT

You're welcome, Carl !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

Reply to this Message