Populating a grid

J

jsuden

I have about 60 managers and each one has two rankings, a 1-4 and a
I-IV (example: manager X: 2, III; manager y: 3, IV.). I then want to
create a function that will populate a table like below:
1 2 3 4
I____|___|___|___|
II___ |___|___|___|
III___|_X |___|___|
IV__ |___|_Y_|___|

I can't figure out the best most automated way to do this....any
suggestions?
 
M

Max

One way ..

Assuming you have in Sheet1,
in cols A to C, data from row1 down

X 2 III
Y 3 IV
Z 1 I
A 4 III

In Sheet2
B1:E1 contains: 1,2,3,4
A2:A5 contains: I,II,III,IV

Put in B2, and array-enter, i.e. press CTRL+SHIFT+ENTER,
(instead of just pressing ENTER):

=IF(ISERROR(MATCH(1,(Sheet1!$C$1:$C$100=$A2)*(Sheet1!$B$1:$B$100=B$1),0)),""
,INDEX(Sheet1!$A$1:$A$100,MATCH(1,(Sheet1!$C$1:$C$100=$A2)*(Sheet1!$B$1:$B$1
00=B$1),0)))

Copy B2 across to E2, fill down to E5 to populate the grid

Adapt the ranges to suit ..
 
H

Herbert Seidenberg

Since there are 60 managers and 16 bins, some managers must
occupy the same bin.
The managers M01 thru M60 and their ratings are listed in columns
and named as shown.
The ratings are repeated on top of the array and named as shown.

.. . . A B C D A B ... D cat_p
.. . . 1 1 1 1 2 2 ... 4 cat_q
M_id cat_n cat_m bin1A bin1B bin1C bin1D bin2A bin2B ... bin4D
M01 4 C M16 M38 M15 M05 M06 M39 ... M28
M02 3 D M46 M24 M08 M53 M45 ...
M03 4 C M47 M10
M04 3 D M21
M05 1 D M23
M06 2 A M40
M07 4 B
.... ... ...
M58 4 B
M59 4 C
M60 4 A

The 60 x 16 array is filled with this formula:
=IF(AND(cat_n=cat_q,cat_m=cat_p),M_id,0)
Select it and
Copy > Paste Special > Value
Goto > Special > Constants > Numbers
Delete > Shift Cells Up
The bins can then be named as shown.
For visual effect, the bins can be entered into a 4 x 4 array
as data validation lists.
 
M

Max

Re-looking at this line in the orig. post
(overlooked its significance earlier, sorry)
I have about 60 managers ...
Click to expand...

the suggested method implicitly assumes a max of 16 unique combo-rankings
Since you have much more than this, and with probably a number duplicate
combo-rankings amongst the 60 mgrs, the suggestion probably won't do it
here.

Try Herbert's response ..
 
Top