Extract & Transpose problem

[kcmuppet]

Hi

I've got a large table of school pupil data (tbl_Pupils) which includes a Pupil ID (text string) and Class name (also text).

On a new sheet I'd like to use a formula to a list Pupil IDs in row 2 from the table based on a class name entered into cell A1. The number of pupils in each class varies.

I've been trying to make use of the formula below to extract the list of pupil IDs in a helper column and then use a transpose formula to copy it to a row:

=INDEX(tbl_Pupils[Pupil ID],MATCH(A1,tbl_Pupils[Class],0),1):INDEX(tbl_Pupils[Pupil ID],MATCH(A1,tbl_Pupils[Class],1),1)

...but although the above formula works for a data-validation drop-down list I can't get it to generate a helper list.

...Could anybody point out what I'm doing wrong or suggest an alternative approach.

Many thanks

 

[kcmuppet]

I found an an example here of another way of doing it,

IF(COUNTIF($A:$A,$D$2) < COLUMNS($G$2:G2),"", INDEX($B:$B, SMALL(IF($A$2:$A$10=$D$2, ROW($A$2:$A$10)), COLUMNS($G$2:G2)))) Ctrl+Shift+Enter
Then copy across

but I'm having trouble making it work with structured references for an excel table.

My Table tbl_Pupils looks like this:

Class Pupil ID
Class 1 Pupil001
Class 1 Pupil002
Class 4 Pupil003
Class 4 Pupil004
Class 1 Pupil005
Class 3 Pupil006
Class 3 Pupil007

So if "Class 1 is in D2, I'd want the list to be Pupil001, Pupil002, Pupil005

The formula in the example works - i.e. puts these values in cellls G2:G5, but when I tried to replace the fixed ranges with structured references I got stuck:

=IF(COUNTIF(tbl_Pupils[Class],$D$2) < COLUMNS($G$2:G2),"", INDEX(tbl_Pupils[Pupil ID], SMALL(IF($A$2:$A$10=$D$2, ROW($A$2:$A$10)), COLUMNS($G$2:G2))))

Would greatly appreciate some help.

Thanks

 

[yky]

In few seconds, a pivotal table can give you something like the following. Is that acceptable? Or you must use a formula to do it?

Class 1

Pupil001

Pupil002

Pupil005

Class 3

Pupil006

Pupil007

Class 4

Pupil003

Pupil004

<tbody>
</tbody>

 

[kcmuppet]

Hi thanks for your reply - I'm looking to make a formula work, so that I don't have to refresh it.


As it stands I'm using:

{=(INDEX(tbl_Pupils[Pupil ID],MATCH(Class_Taught,tbl_Pupils[Class],0),1):INDEX(tbl_Pupils[Pupil ID],MATCH(Class_Taught,tbl_Pupils[Class],1),1)}

applied to a of 1 column and 40 rows named Pupils_in_Class_Taught and then this:

{=IFNA(INDEX(tbl_Pupils[Pupil ID],MATCH(Class_Taught,tbl_Pupils[Class],0),1):INDEX(tbl_Pupils[Pupil ID],MATCH(Class_Taught,tbl_Pupils[Class],1),1),"")}

applied to the target range of 1 row x 40 columns

I'm thinking there must be better way, where 'better' = one step rather than two, and accomodates a variable number of pupils in the class (so that I don't need to have to guess that 40 might be the maximum number of pupils in a class, deal with #N/A, etc.