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

[nskinner821]

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.

 

[Toadstool]

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?

 

[nskinner821]

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.

 

[Toadstool]

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.

	A	B	C	D	E	F	G	H
Name	LoV Source
Alf Carruthers	Swimming
Bert Jones	Sailing
Charlie White	Hiking
Hannah Ross	Orienteering
John Connelly	Archery
John Jackson
John Tripp
Keith Johnson
Lily Smithers
Nigel Thompson
Sarah Pollard
Stella Mitchell
Susan Smith
Vanessa Attwood
Zoe Green

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]Archery[/TD]
[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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:

	A	B	C	D	E	F
Monday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Swimming[/TD]
[TD="align: center"]Sailing[/TD]
[TD="align: center"]Hiking[/TD]
[TD="align: center"]Orienteering[/TD]
[TD="align: center"]Archery[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Alf Carruthers[/TD]
[TD="align: center"]Bert Jones[/TD]
[TD="align: center"]Charlie White[/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]John Connelly[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]John Jackson[/TD]
[TD="align: center"]John Tripp[/TD]
[TD="align: center"]Keith Johnson[/TD]
[TD="align: center"]Lily Smithers[/TD]
[TD="align: center"]Nigel Thompson[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sarah Pollard[/TD]
[TD="align: center"]Stella Mitchell[/TD]
[TD="align: center"]Susan Smith[/TD]
[TD="align: center"]Vanessa Attwood[/TD]
[TD="align: center"]Zoe Green[/TD]

</tbody>

Monday​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=TEXT(SHEET(),"dddd")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2
copy to F23
[/TH]
[TD="align: left"]=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)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

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.

 

[nskinner821]

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!!

 

[Toadstool]

Re: HELP! Formula to identify text in one cell and group in in a new tab?

You're welcome!