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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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)))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top