formula that returns column headings based on multiple criteria

[Jamel]

Below I'm showing a sample of the spreadsheet I'm working with. I'm looking for a formula that
answers the Answer 2 column. I've already successfully created a formula that answers Answer 1.
In the Answer 1 column I capturing the column heading where the first "A" absence appears.
The true column heading I've highlighted in blue. I included a heading over that row just in case
I need to reference that to compare to the days the student attends class.

Answer 2 is a lot more trickier because I have to honor the days of the week each student attends the class.
In Answer 2 I need to capture the column where the first blank appears after the first "A" absence
while honoring the days of the week the student attends the class. In my table below I've answered
all the Answer 2 to show what needs to be the result.

As always any help with this will be greatly appreciated

not sure why all the grid lines are showing.. there are showing before I hit the post button.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
days of week	Answer 1	Answer 2
Student 1	M W
Student 2	T H
Student 3	H
Student 4	M T W H F
Student 5	W
Student 6	H
Student 7	M W F
Student 8	T
Student 9	M

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]F[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"] 3 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"] 5 [/TD]
[TD="align: center"] 8 [/TD]
[TD="align: center"] 9 [/TD]
[TD="align: center"] 10 [/TD]
[TD="align: center"] 11 [/TD]
[TD="align: center"] 12 [/TD]
[TD="align: center"] 15 [/TD]
[TD="align: center"] 16 [/TD]
[TD="align: center"] 17 [/TD]
[TD="align: center"] 18 [/TD]
[TD="align: center"] 19 [/TD]
[TD="align: center"] 22 [/TD]
[TD="align: center"] 23 [/TD]
[TD="align: center"] 24 [/TD]
[TD="align: center"] 25 [/TD]
[TD="align: center"] 26 [/TD]

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

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]23[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]

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

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]

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

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]

</tbody>

Sheet1

​

 

[Jamel]

Don't know how to delete this post that had a bad format. Please look at the post below that contains my current problem.

 

[Jamel]

I’m still having issues with this spreadsheet. The formulas I was provide only seems to work when the absent and return date are within the same month. Below is a table showing the answers I’m looking.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		month	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	APRIL	MAY	MAY	MAY	MAY	MAY	MAY	MAY	MAY	MAY	ABS MONTH	ABS DAY	RETN MONTH	RETN DAY
2	1	day of week	R	F	M	T	W	R	F	M	T	W	R	F	M	T	W	R	F	M	ANSWER	ANSWER	ANSWER	ANSWER
3	2	days of week	18	19	22	23	24	25	26	29	30	1	2	3	6	7	8	9	10	13
4	3	M W					A					A			A						APRIL	24	APRIL	29
5	4	T R				A		A			A							A			APRIL	23	MAY	2
6	5	R						A					A								APRIL	25	MAY	9
7	6	M T W R F						A	A	A	A	A				A			A		APRIL	25	MAY	2
8	7	W					A					A									APRIL	24	MAY	8
9	8	R	A					A					A								APRIL	18	MAY	9
10	9	M W F			A		A		A	A		A		A	A		A				APRIL	22	MAY	10
11	10	T									A										APRIL	30	MAY	7
Sheet1

 

[Jamel]

My problems are occuring in columns W & X

 

[Marcelo Branco]

Try

I used a helper column to make things easier


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

U​

[/TD]
[TD="bgcolor: #DCE6F1"]

V​

[/TD]
[TD="bgcolor: #DCE6F1"]

W​

[/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[TD="bgcolor: #DCE6F1"]

Y​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ABS MONTH​

[/TD]
[TD]

ABS DAY​

[/TD]
[TD]

RETN MONTH​

[/TD]
[TD]

RETN DAY​

[/TD]
[TD]

Helper​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

ANSWER​

[/TD]
[TD]

ANSWER​

[/TD]
[TD]

ANSWER​

[/TD]
[TD]

ANSWER​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

24​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

29​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

23​

[/TD]
[TD]

MAY​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

25​

[/TD]
[TD]

MAY​

[/TD]
[TD]

9​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

25​

[/TD]
[TD]

MAY​

[/TD]
[TD]

2​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

24​

[/TD]
[TD]

MAY​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

18​

[/TD]
[TD]

MAY​

[/TD]
[TD]

9​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

22​

[/TD]
[TD]

MAY​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

APRIL​

[/TD]
[TD]

30​

[/TD]
[TD]

MAY​

[/TD]
[TD]

7​

[/TD]
[TD]

11​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in Y4 copied down (Helper column)
=AGGREGATE(14,6,(COLUMN(C3:T3)/((C$1:T$1=U4)*(C$3:T$3=V4))),1)

Formula in W4 copied down
=INDEX(C$1:T$1,MATCH(1,INDEX((ISNUMBER(MATCH("*"&C$2:T$2&"*",B4,0)))*(COLUMN(C$3:T$3)>Y4)*(C4:T4=""),),0))

Formula in X4 copied down
=INDEX(C$3:T$3,MATCH(1,INDEX((ISNUMBER(MATCH("*"&C$2:T$2&"*",B4,0)))*(COLUMN(C$3:T$3)>Y4)*(C4:T4=""),),0))

Hope this helps

M.

 

[Jamel]

First of all Thanks for the quick response. The sample spreadsheet shown above I had to manufacture on the side so I could show what I needed.

On my real spreadsheet I have additional codes other than just "A". However I want the formulas based only wanting to capture the "A's".

Will it matter that I have additional codes in the spreadsheet?

 

[Marcelo Branco]

I think the formula should work. Try it.

M.

 

[Jamel]

I'm running into a few annomalies were it's not returning the month. Also the Helper field is returning values over 31.

having difficulty figure out if anything different in the data is causing this

 

[Jamel]

Think I've figured it out...

 

[Jamel]

I'm not sure if the formula works if the occurrence span beyond 2 month. For example if I'm absent Feb 22 and dont return until April 3.

 

[Jamel]

Hello,

Sorry to keep bugging you Marcelo Branco

but I'm running into a few anomalies where that formula you gave me isn't working.

for example... I have a student schedule to attend a class monday and thursdays. The student was abscent on the 14th, 18th, 21st, 25th, and the 28th of February.

The student returned on March 4. The return month formula is working correctly by assigning Mar. however the return day is showing the 18 and the helper is showing 25.

The majority of the records the formula is working correctly.

Seems like I'm still having issues when the student return in a different month. Any help will be greatly appreciated.

The formula seems very close to being perfect.