formula that returns column headings based on multiple criteria

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
55
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.



ABCDEFGHIJKLMNOPQRSTUV
days of weekAnswer 1Answer 2
Student 1M W
Student 2T H
Student 3H
Student 4M T W H F
Student 5W
Student 6H
Student 7M W F
Student 8T
Student 9M

<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
 
Last edited:
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.

We need data sample and expected result for this student - for testing purposes.

M.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I hid columns because the spreadsheet is a so large.


Book1
ABXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZDNEKELEMENEO
1FebFebFebFebFebFebFebFebFebFebFebFebMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarMarAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprAprApr
2WHFMTWHFMTWHFMTWHFMTWHFMTWHFMTWHFMTWHFMTWHFMTWHFMTWHFMT
3recordheader1314151819202122252627281456781112131415181920212225262728291234589101112151617181922232425262930meetdaysabscentMonthabscentreturnMonthreturnhelper
587file1AEAET HFeb26Mar2833
591file1AET HFeb28Mar3035
611file1AET HFeb28Mar3035
643file1AEAEAET HFeb21Mar2630
670file1AEM FFeb28Mar2935
686file1AET HFeb28Mar3035
722file1AEW FFeb28Mar2935
773file1AEM FFeb28Mar2935
777file1AEM FFeb28Mar2935
790file1AEAEM WFeb25Mar2732
899file1AET FFeb27Mar2934
959file1AEHFeb28Mar3035
988file1AEHFeb28Mar3035
1030file1AEMFeb25Mar2932
1031file1AEMFeb25Mar2932
1032file1AEMFeb25Mar2932
1033file1AEMFeb25Mar2932
1034file1AEMFeb25Mar2932
1035file1AEMFeb25Mar2932
1036file1AEMFeb25Mar2932
1037file1AEMFeb25Mar2932
1038file1AEMFeb25Mar2932
1039file1AEMFeb25Mar2932
1040file1AEMFeb25Mar2932
Current
 
Upvote 0
the colums in my formula go thru date June 14, but I didnt show those columns since we are there yet in the year

The return month and days is where the error is still occuring
 
Last edited:
Upvote 0
Not clear
Questions:
For which student (row) the formula is not working?
Expected result?
I can't see the column Days of Week.
What is the meaning of AE? In the previous data samples there was only A or blanks...

It's getting difficult to help

M.
 
Last edited:
Upvote 0
Sorry I wasn't more clearer. The spreadsheet above contains records that show errors in every case.

In every case columns EK & EL are working properly. Its showing the first occurrence of AE (Absent Excused) starting from February 13. Column EM & EN is suppose to show the first occurrence a "Blank" shows based on Column DN. Blanks means the kid was present.

In Rows 587, 591, and 611 the return date should of been Monday March 4th. It's instead showing March 28th and 30th.

One thing I notice is the formula works when the return date is within the same month. This means if the student was absent on FEB 13th and return FEB 24 the formula you gave me works just fine. It's not working when the return date is beyond the month.

Hope this makes since.
 
Upvote 0
The solution i suggested in post 14 worked perfectly even when dealing with different months. Unless you can show a scenario (a clear and understandable sample of data) where such a solution does not work, I do not know how to help you anymore.

M.
 
Upvote 0
In Rows 587, 591, and 611 the return date should of been Monday March 4th. It's instead showing March 28th and 30th.

The formulas worked perfectly for me

Scenario (relevant columns)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[TD="bgcolor: #DCE6F1"]
K
[/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD]
[TD="bgcolor: #DCE6F1"]
M
[/TD]
[TD="bgcolor: #DCE6F1"]
N
[/TD]
[TD="bgcolor: #DCE6F1"]
O
[/TD]
[TD="bgcolor: #DCE6F1"]
P
[/TD]
[TD="bgcolor: #DCE6F1"]
Q
[/TD]
[TD="bgcolor: #DCE6F1"]
R
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Feb​
[/TD]
[TD]
Mar​
[/TD]
[TD]
Mar​
[/TD]
[TD]
Mar​
[/TD]
[TD]
Mar​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
W​
[/TD]
[TD]
H​
[/TD]
[TD]
F​
[/TD]
[TD]
M​
[/TD]
[TD]
T​
[/TD]
[TD]
W​
[/TD]
[TD]
H​
[/TD]
[TD]
F​
[/TD]
[TD]
M​
[/TD]
[TD]
T​
[/TD]
[TD]
W​
[/TD]
[TD]
H​
[/TD]
[TD]
F​
[/TD]
[TD]
M​
[/TD]
[TD]
T​
[/TD]
[TD]
W
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
record​
[/TD]
[TD]
Header​
[/TD]
[TD]
13​
[/TD]
[TD]
14​
[/TD]
[TD]
15​
[/TD]
[TD]
18​
[/TD]
[TD]
19​
[/TD]
[TD]
20​
[/TD]
[TD]
21​
[/TD]
[TD]
22​
[/TD]
[TD]
25​
[/TD]
[TD]
26​
[/TD]
[TD]
27​
[/TD]
[TD]
28​
[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
6
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
587​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
AE​
[/TD]
[TD][/TD]
[TD]
AE​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD]
591​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
AE​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD]
611​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
AE​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Solution
Meetdays in column BG
Formulas in columns BG:BK

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
BF
[/TD]
[TD="bgcolor: #DCE6F1"]
BG
[/TD]
[TD="bgcolor: #DCE6F1"]
BH
[/TD]
[TD="bgcolor: #DCE6F1"]
BI
[/TD]
[TD="bgcolor: #DCE6F1"]
BJ
[/TD]
[TD="bgcolor: #DCE6F1"]
BK
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
meetdays​
[/TD]
[TD]
abscentMonth​
[/TD]
[TD]
abscentDay​
[/TD]
[TD]
returnMonth​
[/TD]
[TD]
returnDay​
[/TD]
[TD]
helper​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
M T​
[/TD]
[TD]
Feb​
[/TD]
[TD]
26​
[/TD]
[TD]
Mar​
[/TD]
[TD]
4​
[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD]
M T​
[/TD]
[TD]
Feb​
[/TD]
[TD]
28​
[/TD]
[TD]
Mar​
[/TD]
[TD]
4​
[/TD]
[TD]
14​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD]
M T​
[/TD]
[TD]
Feb​
[/TD]
[TD]
28​
[/TD]
[TD]
Mar​
[/TD]
[TD]
4​
[/TD]
[TD]
14​
[/TD]
[/TR]
</tbody>[/TABLE]


Helper column
Formula in BK4 copied down
=INDEX(COLUMN(C4:BE4),MATCH("AE",C4:BE4,0))

Formula in BG4 copied down
=INDEX(C$1:BE$1,MATCH("AE",C4:BE4,0))

Formula in BH4 copied down
=INDEX(C$3:BE$3,MATCH("AE",C4:BE4,0))

Formula in BI4 copied down
=INDEX(C$1:BE$1,MATCH(1,INDEX(ISNUMBER(SEARCH(C$2:BE$2,BF4))*(COLUMN(C4:BE4)>BK4)*(C4:BE4=""),),0))

Formula in BJ4 copied down
=INDEX(C$3:BE$3,MATCH(1,INDEX(ISNUMBER(SEARCH(C$2:BE$2,BF4))*(COLUMN(C4:BE4)>BK4)*(C4:BE4=""),),0))

M.
 
Upvote 0
Marcelo,

I am so sorry to keep bugging you. You referenced the post on the 14th working and you gave a solution a bit different.

On the post on in the helper formula you used an Aggregate formula, but on you solution above its not aggregate.

I starting there because when I apply the new formula you sent for the helper column I'm getting an N/A#.

I wanted to copy and past a snipet of the entire spreadsheet but the data extends so widely I'm afraid it wont fit.

Is there a way I can send you a sample of the real file and not just an image?
 
Upvote 0
Nevermind. Let me keep plugging away at it... I think its the way I'm inputting things incorrectly...

Sorry for disturbing you.

Thank you so much for you help and patience with me
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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