Formula to identify text in one cell and group in in a new tab?

nskinner821

New Member
Joined
Oct 29, 2019
Messages
3
BLUF - I looked through the threads best I could and did not see this answer, so I am very sorry if this scenario has already been answered.

What I have is a workbook with 6 tabs (one "data" tab and 5 other tabs representing the days of the week). The data tab contains a list of 22 students names in the first column, with the next 5 columns each representing Mon-Fri. In the rows/columns I have a selection of 5 different activities the students will be participating in that day.

What formula can I use that once I select an activity in a cell for that corresponding student and day, it will automatically list that student under that activity on the next workbook tab (for that day?)

My goal is that I can use the "data" tab once a week and quickly select the task for each student by day. In turn, that auto populates on each one of the "day" tabs that will have the 5 activities listed as columns with the names auto-populating under each activity.

Thank you so much for any help.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Re: HELP! Formula to identify text in one cell and group in in a new tab?

Hi nskinner821,

Q. By "In the rows/columns I have a selection of 5 different activities the students will be participating in that day." do you mean each student has one activity for each day of the week, maybe from a list of values? e.g. Ivor Swot can have one activity of Wednesday. In the LoV offerings of Study/Soccer/Yoga/Swimming/Hiking you select one option such as Hiking?
 
Upvote 0
Re: HELP! Formula to identify text in one cell and group in in a new tab?

Correct. Each student will have one activity per day. On the "Set-up" or "data" tab is where each student is listed and next to them I put in a valid value list or drop downs for each one of their daily activities. When I select that students activity drop downs for each day, that is what I am trying to get to auto populate on the other tabs. Example - On the drop down options for Ivor Swot, I select Study for Monday, Soccer for Tuesday, Yoga for Wednesday, Swimming for Thursday, and hiking for Friday. After all selections are made for all the students on the first tab, that is when I want to click on "Monday" tab and I have those activities used as column titles, but I need the students names to populate under each activity.
 
Upvote 0
Re: HELP! Formula to identify text in one cell and group in in a new tab?

Is this what you're looking for?

Here's the first tab called "Data" which has a list off to one side for the LoV selection of each cell. There's no formulae here, just the Data Validation entries from the LoV.

ABCDEFGH
1NameMondayTuesdayWednesdayThursdayFridayLoV Source
2Alf CarruthersSwimmingSailingHikingOrienteeringArcherySwimming
3Bert JonesSailingHikingOrienteeringArcherySwimmingSailing
4Charlie WhiteHikingOrienteeringArcherySwimmingSailingHiking
5Hannah RossOrienteeringArcherySwimmingSailingHikingOrienteering
6John ConnellyArcherySwimmingSailingHikingOrienteeringArchery
7John JacksonSwimmingSailingHikingOrienteeringArchery
8John TrippSailingHikingOrienteeringArcherySwimming
9Keith JohnsonHikingOrienteeringArcherySwimmingSailing
10Lily SmithersOrienteeringArcherySwimmingSailingHiking
11Nigel ThompsonArcherySwimmingSailingHikingOrienteering
12Sarah PollardSwimmingSailingHikingOrienteeringArchery
13Stella MitchellSailingHikingOrienteeringArcherySwimming
14Susan SmithHikingOrienteeringArcherySwimmingSailing
15Vanessa AttwoodOrienteeringArcherySwimmingSailingHiking
16Zoe GreenArcherySwimmingSailingHikingOrienteering

<tbody>
</tbody>
Data

Here's the second tab called "Monday". After completion you can right-cllick the tab name, copy and move-to-end to create the next tab which should be renamed "Tuesday". Do this for each tab through Friday and it will use the Sheet number to figure out which day to search:

ABCDEF
1MondaySwimmingSailingHikingOrienteeringArchery
2Alf CarruthersBert JonesCharlie WhiteHannah RossJohn Connelly
3John JacksonJohn TrippKeith JohnsonLily SmithersNigel Thompson
4Sarah PollardStella MitchellSusan SmithVanessa AttwoodZoe Green

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Monday

Worksheet Formulas
CellFormula
A1=TEXT(SHEET(),"dddd")
B2
copy to F23
=IFERROR(INDEX(Data!$A$1:$A$23,AGGREGATE(15,6,ROW(Data!$A$2:$A$23)/(INDEX(Data!$B$2:$F$23,,SHEET()-1)=B$1),ROW()-1)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Re: HELP! Formula to identify text in one cell and group in in a new tab?

I thought I should provide some explanation.

Monday is Sheet 2 so =TEXT(SHEET(),"dddd") shows the second day of the week, Monday.

The
=IFERROR(INDEX(Data!$A$1:$A$23,AGGREGATE(15,6,ROW(Data!$A$2:$A$23)/(INDEX(Data!$B$2:$F$23,,SHEET()-1)=B$1),ROW()-1)),"")
is trickier to explain ;)

The IFERROR catches the #NUM error when you run out of entries for that day/activity.

INDEX(Data!$A$1:$A$23 retrieves the student name.

AGGREGATE(15,6, ....... ROW()-1) uses the SMALL function (15) and ignores errors (6) to find the 1st then 2nd the 3rd, etc match, as ROW()-1 starts on row 2 so gives 1 then on row 3 the 3-1 becomes 2nd, etc.

ROW(Data!$A$2:$A$23)/ tells the AGGREGATE to check rows 2 to 23, the "/" says divide each row number by the following comparison, so if the comparison is successful you'll get a 1 and the row number divided by 1 is the row number. If the comparison fails you'll get a 0 which divided into the row number gives #DIV/0 which the AGGREGATE option 6 "ignore errors" will then ignore.

(INDEX(Data!$B$2:$F$23,,SHEET()-1)=B$1) comparison says retrieve from the DATA tab B2 to F23 the entry on every row (the two commas mean row is not specified) but using column SHEET()-1 so Monday is 2-1=1 and it checks column B, Tuesday is 3-1=2 and it checks column C, etc. It compares this to the heading with the activity and if it finds a match AGGREGATE gives the row which the original INDEX then uses to get the student name.
 
Upvote 0
Re: HELP! Formula to identify text in one cell and group in in a new tab?

Wow. I got it to work. Thank you so much!!
 
Upvote 0
Re: HELP! Formula to identify text in one cell and group in in a new tab?

You're welcome!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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