Find all cells starting with "SS" - Array Formula with Partial matches????

[tom.walford]

Hi there,

I am trying to retrieve all three of the cells in a range that start with the letters SS (Study Session). The sessions are numbered SS01 to SS30 in a much bigger sheet.

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]Num Study[/TD]
[TD]Num Careers[/TD]
[TD]Study 1[/TD]
[TD]Study 2[/TD]
[TD]Study 3[/TD]
[TD]Mon 1[/TD]
[TD]Mon 2[/TD]
[TD]Mon 3[/TD]
[TD]Mon 4[/TD]
[TD]Mon 5[/TD]
[TD]Mon 6[/TD]
[TD]Mon 7[/TD]
[TD]Mon 8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Student[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]SS01[/TD]
[TD]SS02[/TD]
[TD]SS09[/TD]
[TD]12A/Ma1[/TD]
[TD]12AMa/1[/TD]
[TD]SS01[/TD]
[TD]SS02[/TD]
[TD]12C/Ph1a[/TD]
[TD]12C/Ph1a[/TD]
[TD]C[/TD]
[TD]SS039[/TD]
[/TR]
</tbody>[/TABLE]

Let's assume that this particular student's three study periods are on Monday (period 3,4 and 8) I would want D2, E2 and F2 to say SS01, SS02 and SS09 respectively. The rest of the timetable goes from Mon 9 through to Friday 9 in a very large worksheet. Each students study sessions are at different times with them picking up three of the sessions from the thirty available.
I have already got the counter functions in B2 and C2 to work, but cannot get the sessions to copy into the three columns to the left of the table.

I assume that this is going to need some sort of array function, but my brain is not on the same wavelength as Excel today - any help would be very much appreciated.

Many thanks

Tom.

 

[Fluff]

How about in D2 & dragged right
=INDEX($G2:$N2,AGGREGATE(15,6,(COLUMN($G2:$N2)-COLUMN($G2)+1)/(LEFT($G2:$N2,2)="SS"),COLUMNS($A$1:A$1)))

 

[tom.walford]

You are a legend!

Many thanks

Tom.

 

[Fluff]

You're welcome & thanks for the feedback