Excel work challenge - writing specific formulas and matching

[JMK22]

Hello!
I need help with this excel work challenge. I need some tutoring as I do not even know how to start step 1.
All instructions are on TAB 1 of this sheet.

Below is a link to sheet example. I put in google sheets as I did not have another way to share the excel file through the posting.

Mock Excel Validation_1.4.22.xlsx

Thanks in advance.
JK

 

[Joe4]

Welcome to the Board!

Many people may be hesitant to download worksheets from the web.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JMK22]

Welcome to the Board!

Many people may be hesitant to download worksheets from the web.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you very much for the help!

 

[Joe4]

I see that you started another post this morning. Is that the same question as this one?
If so, would you like me to merge the two threads together, or are you going to re-post those details to this thread?

 

[JMK22]

I need help with the formula for step 3 specifically.
I think that I need to correct the formula in Column L to make it match properly, but I just can't seem to figure out how to correct it.

working copy_ IR Analyst Mock Excel Validation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Step 1: Copy appropriate data from School Original report into the white columns on the Validation tab. Don't overwrite or change formulas in blue columns. Drag formulas to bottom of data and/or delete any extras.					10/2/2021	Step 2: Write a formula to calculate age as of 10/2/2021 (period end date).			Step 3: Edit formula in either column K or column L to create a "Match" in column L
2				Match	5			Match	5		Match	0
3				Anomaly	0			Anomaly	0		Anomaly	5
4	STUDENTID	ID No.	Name	K12 Name	Match?	Age	K12 DOB	Age	Match?	Gender	K12 Gender	Match?	Gr
5	120898	000000000671	Holden Caulfield	Holden Caulfield	Match	13	1/14/2008	13	Match	M	Male	Anomaly	7
6	212330	000000002481	Barney Rubble	Barney Rubble	Match	12	10/25/2008	12	Match	M	Male	Anomaly	7
7	362110	000000001816	Wilma Flintstone	Wilma Flintstone	Match	12	4/13/2009	12	Match	F	Female	Anomaly	7
8	395488	000000000945	Rhett Butler	Rhett Butler	Match	13	12/27/2007	13	Match	N	Non-Binary	Anomaly	8
9	391459	000000001494	Bruce Banner	Bruce Banner	Match	13	8/20/2008	13	Match	M	Male	Anomaly	8
10
Validation

Cell Formulas
Range	Formula
E2,L2,I2	E2	=COUNTIF(E5:E857,"Match")
E3,L3,I3	E3	=COUNTIF(E5:E857,"Anomaly")
D5:D9	D5	=CONCATENATE(XLOOKUP($A5,'All Students'!$F:$F,'All Students'!$H:$H)," ",XLOOKUP($A5,'All Students'!$F:$F,'All Students'!$G:$G))
E5:E9,L5:L9	E5	=IF(D5=C5,"Match","Anomaly")
G5:G9	G5	=VLOOKUP($A5,'All Students'!$F:$L,7,FALSE)
H5:H9	H5	=DATEDIF(G5,DATE(2021,10,2),"y")
I5:I9	I5	=IF(H5=F5,"Match","Anomaly")
K5:K9	K5	=XLOOKUP($A5,'All Students'!$F:$F,'All Students'!$J:$J)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O,E:E,I:I,L:L,U:U,AA:AA	Cell Value	="Anomaly"	text	NO
A:A	Cell Value	duplicates	text	NO

Also this is the range for the xlookup for the formula in column K....

working copy_ IR Analyst Mock Excel Validation.xlsx
	F	G	H	I	J
4	1	2	3	4	5
5	STUDENTID	STUDENTLASTNAME	STUDENTFIRSTNAME	STUDENTMIDDLENAME	STUDENTGENDER
6	120898	Caulfield	Holden	Eugene	Male
7	194555	Stevens	Samantha	Louise	Female
8	212330	Rubble	Barney	Richard	Male
9	229350	O'Hara	Scarlett	Nicole	Female
10	362110	Flintstone	Wilma	Elizabeth	Female
11	395488	Butler	Rhett		Non-Binary
12	391459	Banner	Bruce	Nicholas	Male
13	593451	Golightly	Holly	Ruby	Female
14	619356	Parker	Peter		Male
15	739701	Haskell	Eddie	James	Male
16	892085	Buchanan	Daisy		Female
17	928024	Prince	Diana	Catherine	Female
18	944784	Brown	Charles	Linus	Male
19	949276	Finch	Atticus	D.	Male
20	968925	Montoya	Inigo	Raphael	Male
21	984505	Gray	Dorian		Male
22	989522	Scott	Michael	Gary	Male
23
All Students

Thanks in advance for any help.

 

[JMK22]

I see that you started another post this morning. Is that the same question as this one?
If so, would you like me to merge the two threads together, or are you going to re-post those details to this thread?

I just saw your response here. I re-posted those details to this thread and managed to get the mini-sheet add-in working. Thanks so much!

 

[Joe4]

What exactly is supposed to be returned in column L?
What are the options?

What exactly is the criteria to be used in column L to determine if there is a match or not?

 

[JMK22]

What exactly is supposed to be returned in column L?
What are the options?

What exactly is the criteria to be used in column L to determine if there is a match or not?

It looks like to me the crteria is "IF K5 = J5" ...? Possibly?

This is the original data set that I had to use to fill in columns A,B,C,F,J,

working copy_ IR Analyst Mock Excel Validation.xlsx
	A	B	C	D	E	F	G	H	I	J
1	STUDENTID	ID No.	Name	Age	Rc	Gender	Gr	Entry Date	Exit Date	ADM
2	120898	000000000671	Holden Caulfield	13	W	M	7	8/1/2021	9/25/2021	0.8
3	212330	000000002481	Barney Rubble	12	W	M	7	8/1/2021		1.0
4	362110	000000001816	Wilma Flintstone	12	W	F	7	8/1/2021		1.0
5	395488	000000000945	Rhett Butler	13	W	N	8	8/1/2021		1.0
6	391459	000000001494	Bruce Banner	13	W	M	8	8/1/2021	9/10/2021	0.3
7	391459	000000001494	Bruce Banner	13	W	M	8	9/25/2021		0.2
8	593451	000000002801	Holly Golightly	10	W	F	4	8/4/2021		1.0
9	739701	000000001069	Eddie Haskell	13	W	M	7	8/1/2021		1.0
10	892085	000000000215	Daisy Buchanan	12	B	F	6	8/1/2021		1.0
11	928024	000000001356	Diana Prince	12	W	F	8	8/1/2021		1.0
12	944784	000000002951	Charles Brown	14	W	M	8	8/1/2021	11/15/2021	1.0
13	968925	000270000271	Inigo Montoya	13	B	F	7	8/1/2021		1.0
14	984505	000000002506	Dorian Gray	10	W	M	4	8/1/2021		1.0
15	989522	000000001645	Michael Scott	13	W	M	8	8/1/2021		1.0
School Original

 

[Joe4]

It looks like to me the crteria is "IF K5 = J5" ...? Possibly?

I don't think that you answered my questions completely.
If K5=J5 then what is supposed to happen?

We need you to tell us exactly what the criteria for this column is, and exactly what is supposed to happen when that criteria is met?
We need to know exactly what you are trying to build here (it is not clear to me from what you have posted).
Remember, we do not know the whole background of this assignment you are working on, we just know what you have shared with us.