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

tom.walford

New Member
Joined
Nov 2, 2010
Messages
30
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.

ABCDEFGHIJKLMN
1NAMENum StudyNum CareersStudy 1Study 2Study 3Mon 1Mon 2Mon 3Mon 4Mon 5Mon 6Mon 7Mon 8
2Student31SS01SS02SS0912A/Ma112AMa/1SS01SS0212C/Ph1a12C/Ph1aCSS039

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
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)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,387
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top