List all those on 400, List all those on 399, etc etc

[palaeontology]

I have a list of student names in Entry!B8:B400 ... not all cells in that range will have a name in it

I have a score (somewhere between 400 and 200) allocated to each of them .. the scores are in Entry!CA8:CA400

Some students will likely have the same score as another student, so scores are not unique to one student, and some scores might not have been achieved by anyone.

In SAI!D2:D202 I have the numbers 400 down to 200.

What I would like is to have the students listed (one name per cell) to the right of the number they achieved ... I'd prefer to do all this by formula, if possible, rather than vba, but if vba is the only way to do it, that's great too.

example ...

on sheet called Entry ...

	Column B	Column AC
row 8	Abbas, Kasey	386
row 9	Balmer, Ashleigh	392
row 10	Bishop, Hamish	382
row 11	Bridge, Liam	392
row 12	Bultitude, Hamish	400
row 13	Charlesworth, Izzy	387
row 14	Coleman, Adam	396
row 15	Douglas, Katelin	395
row 16	Gahlot, Bhanu	384
row 17	Geffen, Josie	388
row 18	Hatton, Callum	389
row 19	Jeffreys, Madi	391
row 20	Kumar, Aniket	397
row 21	Mao, Yanze	387
row 22	Meyers, Bailey	388
row 23	Powrie, Olivia	395
row 24	Ram, Shamant	393

<tbody>
</tbody>

and on sheet called SAI ...

	Column D
row 2	400	Bultitude, Hamish
row 3	399
row 4	398
row 5	397	Kumar, Aniket
row 6	396	Coleman, Adam
row 7	395	Douglas, Katelin	Powrie, Olivia
row 8	394
row 9	393	Ram, Shamant
row 10	392	Balmer, Ashleigh	Bridge, Liam
row 11	391	Jeffreys, Madi
row 12	390
row 13	389	Hatton, Callum
row 14	388	Geffen, Josie	Meyers, Bailey
row 15	387	Mao, Yanze	Charlesworth, Izzy
row 16	386	Abbas, Kasey
row 17	385
row 18	384	Gahlot, Bhanu
row 19	383
row 20	382	Bishop, Hamish
row 21	381
row 22	380

<tbody>
</tbody>

Is there a way to do this ?

Kind regards,

Chris

 

[Peter_SSs]

Try this formula, copied across and down.

Excel Workbook
	D	E	F	G
2	400	Bultitude, Hamish
3	399
4	398
5	397	Kumar, Aniket
6	396	Coleman, Adam
7	395	Douglas, Katelin	Powrie, Olivia
8	394
9	393	Ram, Shamant
10	392	Balmer, Ashleigh	Bridge, Liam
11	391	Jeffreys, Madi
12	390
13	389	Hatton, Callum
14	388	Geffen, Josie	Meyers, Bailey
15	387	Charlesworth, Izzy	Mao, Yanze
16	386	Abbas, Kasey
17	385
18	384	Gahlot, Bhanu
19	383
20	382	Bishop, Hamish
SAI

 

[palaeontology]

Hi Peter,

as usual, you're completely brilliant.

Thankyou, so much, for that, and for responding so quickly too.

Very kind regards,

Chris

 

[Peter_SSs]

Glad it helped.