Looking For Direction...

[Sabotage1945]

Hi everyone,

I'm looking for some suggestions on how I may be able to do this... I'm
willing to search the help etc if you could just point me in the right
direction.

I've got a file with two worksheets containing student records (ID,
courses, grades, etc). One sheet contains 'Students who were tutored'
(200 or so records) and the other 'Students who were not tutored' (800
or so records).

Now I'd like to match up (pair up) those students of same sex, course
and age (I would guess I would use "sort") from both of these into one
new sheet - while maintaining a RANDOM selection for those 'Students
who were not tutored' of the same sex etc.

Is this kind of thing possible in Excel? I've tried many different
ways to match them

Thanks in advance,
Sab.

 

[Max]

Just some thoughts ..

Try a play along these lines

Assume this table is in
sheet named: T, in A15
(T = Tutored)

ID Sex Age Course
111 M 23 ABC
112 F 22 XYZ
113 F 22 DEF
114 M 24 XYZ

And this table is in
sheet named: NT, in A117
(NT = Not Tutored)

ID Sex Age Course
211 M 23 DEF
212 F 22 ABC
213 M 23 XYZ
214 F 23 XYZ
215 M 24 DEF
216 F 22 XYZ
217 M 23 DEF
218 F 23 ABC
219 M 22 XYZ
220 F 22 DEF
221 M 24 ABC
222 F 24 XYZ
223 M 22 DEF
224 F 24 ABC
225 M 22 XYZ
226 F 22 DEF

(The ratio of the sample students' IDs # in T and NT is 4:16 or 1:4,
as per your posted figures of 200 tutored: 800 untutored)

In sheet: NT
-----------------
We'll use 2 empty cols to the right of the table in A117

Put in G2: =RAND()
Copy down to G17
Name the range G2:G17 as: TBL1

Select H2:H17
Put in the formula bar: =RANK(TBL1,TBL1)
and array-enter, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

In a new sheet
---------------------
Put in A1:E1, the labels: T, NT, Sex, Age, Course

Put in A2: =OFFSET(T!$A$1,INT((ROW(A1)+3)/4),)
Put in B2: =OFFSET(NT!$A$1,NT!H2,)

Put in C2:
=--(VLOOKUP($A2,T!$A$2:$D$5,COLUMN(B1),0)=VLOOKUP($B2,NT!$A$2:$D$17,COLUMN(B
1),0))
Copy C2 across to E2

Put in F2: =SUM(C2:E2)

Select A2:F2, copy down to F17

Put in F1: =SUM(F2:F17)/(COUNTA(C:E)-3)
Format F1 as percentage

What you'll get is:

In col A: The IDs from T repeated 4 times each
In col B: A random match of all the 16 IDs from NT
(against the IDs in col A, in the ratio of 1:4)

In cols C to E: A series of zeros and 1's, depending
on whether the Sex, Age and Course of
student IDs in col A (from T) match those in col B (from NT)
(zero = FALSE, unmatched ; "1" = TRUE, matched)

Col F totals up the figs in cols C to E, and
F1 provides a % measure of the overall match in cols C to E
(the higher the percentage in F1, the better the overall match)

Now to play .. : Press F9 to recalculate
Each press will generate a new randomized splash in col B
Watch the % figure in F1 (it'll fluctuate up and down)
... then decide when the % is high enough to stop the F9 re-calc <g>

Just copy and freeze the results elsewhere
with a paste special > values > OK