formula that returns column headings based on multiple criteria

Jamel · Oct 25, 2018

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

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

Marcelo Branco · Mar 18, 2019

Jamel said:
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.

Jamel · Mar 18, 2019

I hid columns because the spreadsheet is a so large.

Book1

A

B

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

DN

EK

EL

EM

EN

EO

1

Feb

Mar

Apr

2

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

W

H

F

M

T

3

record

header

13

14

15

18

19

20

21

22

25

26

27

28

1

4

5

6

7

8

11

12

13

14

15

18

19

20

21

22

25

26

27

28

29

1

2

3

4

5

8

9

10

11

12

15

16

17

18

19

22

23

24

25

26

29

30

meetdays

abscentMonth

abscent

returnMonth

return

helper

587

file1

AE

T H

Feb

26

Mar

28

33

591

file1

AE

T H

Feb

28

Mar

30

35

611

file1

AE

T H

Feb

28

Mar

30

35

643

file1

AE

T H

Feb

21

Mar

26

30

670

file1

AE

M F

Feb

28

Mar

29

35

686

file1

AE

T H

Feb

28

Mar

30

35

722

file1

AE

W F

Feb

28

Mar

29

35

773

file1

AE

M F

Feb

28

Mar

29

35

777

file1

AE

M F

Feb

28

Mar

29

35

790

file1

AE

M W

Feb

25

Mar

27

32

899

file1

AE

T F

Feb

27

Mar

29

34

959

file1

AE

H

Feb

28

Mar

30

35

988

file1

AE

H

Feb

28

Mar

30

35

1030

file1

AE

M

Feb

25

Mar

29

32

1031

file1

AE

M

Feb

25

Mar

29

32

1032

file1

AE

M

Feb

25

Mar

29

32

1033

file1

AE

M

Feb

25

Mar

29

32

1034

file1

AE

M

Feb

25

Mar

29

32

1035

file1

AE

M

Feb

25

Mar

29

32

1036

file1

AE

M

Feb

25

Mar

29

32

1037

file1

AE

M

Feb

25

Mar

29

32

1038

file1

AE

M

Feb

25

Mar

29

32

1039

file1

AE

M

Feb

25

Mar

29

32

1040

file1

AE

M

Feb

25

Mar

29

32

Current

Jamel · Mar 18, 2019

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

Marcelo Branco · Mar 18, 2019

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.

Jamel · Mar 19, 2019

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.

Jamel · Mar 20, 2019

Marcelo,

Were you able to come up with anything? I hope my previous message made since.

Marcelo Branco · Mar 20, 2019

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.

Marcelo Branco · Mar 21, 2019

Jamel said:
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.

Jamel · Mar 21, 2019

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?

Jamel · Mar 21, 2019

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

formula that returns column headings based on multiple criteria

Jamel

Board Regular

Marcelo Branco

MrExcel MVP

Excel Facts

Jamel

Board Regular

Jamel

Board Regular

Marcelo Branco

MrExcel MVP

Jamel

Board Regular

Jamel

Board Regular

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

Jamel

Board Regular

Jamel

Board Regular

Similar threads

Forum statistics

Share this page

formula that returns column headings based on multiple criteria

Board Regular

MrExcel MVP

Excel Facts

Board Regular

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock