Removing Absent People from a List

[Brown]

Good morning,
I am trying to wrap my mind around this, and I know someone here can send me in the right direction. I have tried xlookup and if formulas but I am not getting what I need.
I have two lists of students, one list is daily absences and one is After School Club. I need the students on the Absence sheet to be removed from the After School Club sheet. Student ID will be the connection between the two sheets not names. What will be the easiest way to make this happen? I will need to build a sheet to give to a school admin to check this, and recreate daily.

Thank you for your thoughts.
Brown

 

[JamesCanale]

MrExcelPlayground24.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	SHEET ABSENCE LIST						SHEET CLUB LIST			SHEET TODAYS CLUB SHEET
2
3	Name	Grade	ID	Date	Nonsense		ID	Club		Date	2/28/2025
4	John	1	10	2/27/2025	Puke		10	Checkers
5	Mary	2	11	2/27/2025	Puke		10	Football		Club	Students Allowed Today
6	Sally	3	12	2/27/2025	Fever		11	Football		Checkers	10, 15, 18
7	Fred	4	13	2/27/2025	Fever		12	Football		Football	10, 11, 14, 15, 16, 17
8	Jamie	5	14	2/27/2025	Lazy		14	Football
9	Sally	3	12	2/28/2025	Fever		15	Checkers
10	Fred	4	13	2/28/2025	Fever		15	Football
11							16	Football
12							17	Football
13							18	Checkers
Sheet2

Cell Formulas
Range	Formula
J6:J7	J6	=UNIQUE(Table2[Club])
K6:K7	K6	=LET(sicktoday,FILTER(Table1[ID],Table1[Date]=$K$3),clubroster,FILTER(Table2[ID],Table2[Club]=J6),ok,FILTER(clubroster,ISNA(XMATCH(clubroster,sicktoday,0))),TEXTJOIN(", ",TRUE,ok))
Dynamic array formulas.

 

[Brown]

Thank you for that, but is there a way to just remove the absent students from the after school club list? I need to be left with an after school club list of kids present today.
Brown

 

[Micron]

Hmm, I shouldn't have deleted my response! If you can use code, it should be doable. What's the goal - removing the values only or removing the whole row? If row, it will likely mess up your alternate row colour. You could manually fix it, or additional code should be able to fix it.

 

[Brown]

Hmm, I shouldn't have deleted my response! If you can use code, it should be doable. What's the goal - removing the values only or removing the whole row? If row, it will likely mess up your alternate row colour. You could manually fix it, or additional code should be able to fix it.

I am afraid code would make this more difficult for the person requesting this. What I would ultimately like to have is a list of students on the After School Club sheet that are present that day.
I was able to get this xlookup to return a student from the absent sheet in column D. This just places the absent students in column D, it doesnt leave me with a list of present students.
Thanks
Brown

 

[JamesCanale]

MrExcelPlayground24.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	SHEET ABSENCE LIST						SHEET CLUB LIST			SHEET TODAYS CLUB SHEET
2
3	Name	Grade	ID	Date	Nonsense		ID			Date	2/27/2025
4	John	1	10	2/27/2025	Puke		10
5	Mary	2	11	2/27/2025	Puke		11			People who can't club	People who can club
6	Sally	3	12	2/27/2025	Fever		12			10	15
7	Fred	4	13	2/27/2025	Fever		14			11	16
8	Jamie	5	14	2/27/2025	Lazy		15			12	17
9	Sally	3	12	2/28/2025	Fever		16			14	18
10	Fred	4	13	2/28/2025	Fever		17
11							18
12
Sheet2

Cell Formulas
Range	Formula
J6:J9	J6	=LET(sicktoday,FILTER(Table1[ID],Table1[Date]=$K$3),clubroster,Table24[ID],ok,FILTER(clubroster,ISNUMBER(XMATCH(clubroster,sicktoday,0))),ok)
K6:K9	K6	=LET(sicktoday,FILTER(Table1[ID],Table1[Date]=$K$3),clubroster,Table24[ID],ok,FILTER(clubroster,ISNA(XMATCH(clubroster,sicktoday,0))),ok)
Dynamic array formulas.

 

[Micron]

OK. I'm not a formula guy so can't help there. Not sure why running code would be difficult but if you change your mind, this seems to work.

Sub RemoveAbsent()
Dim ws As Worksheet
Dim rng As Range
Dim lrow As Long, i As Integer
Dim IDtoFind As Variant

lrow = Sheets("Sheet9").Cells(Rows.Count, "A").End(xlUp).row 'absence sheet
Set ws = Sheets("Sheet3") 'attendance sheet
For i = 2 To lrow
    IDtoFind = Range("C" & i)
    Set rng = ws.Cells.Find(What:=IDtoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rng Is Nothing Then rng.EntireRow.ClearContents
Next

End Sub

ABSCENCE

CLUBS BEFORE

CLUBS AFTER

 

[Brown]

OK. I'm not a formula guy so can't help there. Not sure why running code would be difficult but if you change your mind, this seems to work.

Sub RemoveAbsent()
Dim ws As Worksheet
Dim rng As Range
Dim lrow As Long, i As Integer
Dim IDtoFind As Variant

lrow = Sheets("Sheet9").Cells(Rows.Count, "A").End(xlUp).row 'absence sheet
Set ws = Sheets("Sheet3") 'attendance sheet
For i = 2 To lrow
    IDtoFind = Range("C" & i)
    Set rng = ws.Cells.Find(What:=IDtoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rng Is Nothing Then rng.EntireRow.ClearContents
Next

End Sub

ABSCENCE
View attachment 122826 CLUBS BEFORE View attachment 122827 CLUBS AFTER View attachment 122828

Thank you. It is not me that I am worried about it is the person requesting this. I need to find a way that the beginner level Excel user can quickly, within 10-15 minutes, create a list of students that are present for the after school clubs each day.
I appreciate your time and effort, and teaching me a little more about Excel.
Brown

 

[LegendaryAxe]

Not sure if this is what you're looking for, but you could add a filter rather than deleting the rows.

EMT1844-Part02.xlsx
	A	B	C	D	E	F	G	H	I
1	Absences			After School Club			After School Club minus Absences
2
3	Student Name	Perm ID		Student ID	Grade Level		Student ID	Grade Level
4	Jeff	1424940		1413847	3		1321611	4
5	Max	1449196		1402175	3		1354424	5
6	Dave	1448169		1321611	4		1371843	6
7	Rich	1402175		1354424	5
8	Kevin	1413847		1371843	6
9				1449196	1
10				1448169	2
Imported Text Files

Cell Formulas
Range	Formula
G4:H6	G4	=FILTER(D4:E10,ISNA(MATCH(D4:D10,B4:B8,0)))
Dynamic array formulas.

 

[LegendaryAxe]

Thank you. It is not me that I am worried about it is the person requesting this. I need to find a way that the beginner level Excel user can quickly, within 10-15 minutes, create a list of students that are present for the after school clubs each day.
I appreciate your time and effort, and teaching me a little more about Excel.
Brown

This shouldn't be an issue as long as you can set up the macro yourself. Once you enter the code provided, you can assign it to a button on the worksheet. All the user would then have to do it is click on the button for the code to run.