formula to indicate a cell based on other cell value

[DHayes]

Good day,
I am looking for a formula to indicate what training a staff member still has to attend. Below is the training schedule. The T indicates the training they need to attend. The A indicates they have attended the training. What I would like is the list of the staff member that still has to attend training.
below example.

	Course 1	Course 2	Course 3	Course 4	Course 5
	2	0	3	1	1	1	3	1	1	1
Employee Code	T	A	T	A	T	A	T	A	T	A	T	A
90108226	1						1				2	0
70885239			1								1	0
90089568											0	0
90043158	1				1	1	1	1			3	2
60865961											0	0
60865986			1				1				2	0
60873999											0	0
70887266									1		1	0
90013032			1	1						1	1	2
Result
90108226	Course 1
70885239	Course 2
90089568
90043158	Course 1
60865961
60865986	Course 2	Course 4
60873999
70887266	Course 5
90013032

<tbody>
</tbody>

 

[Col Delane]

G'day

I've not got quite what you've shown in your post as the desired end result, but am close.
BTW, I got a different result than you for several employees (90108226, 90043158, 90013032)?


I added a helper column (in P) to your first table in order to ascertain only those employees who had outstanding training and provide an index to allow them to be looked up in order.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

2 ​

Course 1​

Course 2​

Course 3​

Course 4​

Course 5​

Count​

3 ​

2 ​

-​

3 ​

1 ​

1 ​

1 ​

3 ​

1 ​

1 ​

1 ​

10 ​

4 ​

4 ​

Employee Code

T​

A​

T​

A​

T​

A​

T​

A​

T​

A​

T​

A​

T Index​

5 ​

90108226​

1​

1​

2​

-​

1​

6 ​

70885239​

1​

1​

-​

2​

7 ​

90089568​

-​

-​

-​

8 ​

90043158​

1​

1​

1​

1​

1​

3​

2​

3​

9 ​

60865961​

-​

-​

-​

10 ​

60865986​

1​

1​

2​

-​

4​

11 ​

60873999​

-​

-​

-​

12 ​

70887266​

1​

1​

-​

5​

13 ​

90013032​

1​

1​

1​

1​

2​

6​

14 ​

15 ​

Outstanding Training Courses

10 ​

16 ​

90108226​

Course 1​

-​

-​

Course 4​

-​

2​

1 ​

17 ​

70885239​

-​

Course 2​

-​

-​

-​

1​

2 ​

18 ​

90043158​

Course 1​

-​

Course 3​

Course 4​

-​

3​

3 ​

19 ​

60865986​

-​

Course 2​

-​

Course 4​

-​

2​

4 ​

20 ​

70887266​

-​

-​

-​

-​

Course 5​

1​

5 ​

21 ​

90013032​

-​

Course 2​

-​

-​

-​

1​

6 ​

22 ​

-​

-​

-​

-​

-​

-​

-​

7 ​

23 ​

-​

-​

-​

-​

-​

-​

-​

8 ​

24 ​

-​

-​

-​

-​

-​

-​

-​

9 ​

25 ​

-​

-​

-​

-​

-​

-​

-​

10 ​

<tbody>
</tbody>

Sheet: .

<tbody>
</tbody>

P5 = IF( $N5 > 0, COUNTIFS( $N$5:$N5, ">0" ), 0 )
C16 = IFERROR( INDEX( $C$5:$C$13, MATCH( P16, $P$5:$P$13, 0 ) ), "-" )
D16 = IF( IFERROR( INDEX( D$5:D$13, MATCH( $C16, $C$5:$C$13, 0 ) ), 0 ), D$2, "-" )
N16 = COUNTIFS( $D16:$M16, "Course*" )

Does this do the job well enough for you?

 

[DHayes]

good day Col Delane,
It is almost there. If you look at 90089568 This person has a T and A for courses 3 & 4. The result I would like to get is only course 1. In this scenario they were allocated the training but has not attended it.
So in a nutshell I only want to know who has been allocated training and has not attended.
I hope it is a little bit clearer.

 

[Col Delane]

Ooops - I forgot about that condition! I'll keep workin'.

If you look at 90089568 This person has a T and A for courses 3 & 4. The result I would like to get is only course 1. In this scenario they were allocated the training but has not attended it.

Shouldn't this be 90043158?

 

[DHayes]

My Apologies that is correct.

 

[Col Delane]

Try this.

A ​

B​

C ​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

2 ​

Course 1​

Course 2​

Course 3​

Course 4​

Course 5​

Count​

Count​

Count​

3 ​

2 ​

-​

3 ​

1 ​

1 ​

1 ​

3 ​

1 ​

1 ​

1 ​

10 ​

4 ​

7 ​

4 ​

Employee Code

T​

A​

T​

A​

T​

A​

T​

A​

T​

A​

T​

A​

T to do​

T Index​

5 ​

90108226​

1​

1​

2​

-​

2​

1​

6 ​

70885239​

1​

1​

-​

1​

2​

7 ​

90089568​

-​

-​

-​

-​

8 ​

90043158 ​

1​

1​

1​

1​

1​

3​

2​

1​

3​

9 ​

60865961 ​

-​

-​

-​

-​

10 ​

60865986​

1​

1​

2​

-​

2​

4​

11 ​

60873999 ​

-​

-​

-​

-​

12 ​

70887266 ​

1​

1​

-​

1​

5​

13 ​

90013032 ​

1​

1​

1​

1​

2​

-​

-​

14 ​

15 ​

Outstanding Training Courses

7 ​

16 ​

90108226​

Course 1​

-​

-​

-​

-​

-​

Course 4​

-​

-​

-​

2​

1 ​

17 ​

70885239​

-​

-​

Course 2​

-​

-​

-​

-​

-​

-​

-​

1​

2 ​

18 ​

90043158​

Course 1​

-​

-​

-​

-​

-​

-​

-​

-​

-​

1​

3 ​

19 ​

60865986 ​

-​

-​

Course 2​

-​

-​

-​

Course 4​

-​

-​

-​

2​

4 ​

20 ​

70887266 ​

-​

-​

-​

-​

-​

-​

-​

-​

Course 5​

-​

1​

5 ​

21 ​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

6 ​

22 ​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

7 ​

23 ​

- ​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

8 ​

24 ​

- ​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

9 ​

25 ​

- ​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

10 ​

<tbody>
</tbody>

Sheet: .

<tbody>
</tbody>

Data Table helper columns
New column at P ("T to do") to exclude any courses both scheduled and attended, as well as those attended when not scheduled (like 90013032 Course 5):
P5 = SUM( MAX(0, D5 - E5), MAX( 0, F5 - G5 ), MAX(0, H5 - I5 ), MAX(0, J5 - K5), MAX( 0, L5 - M5 ) )

"T Index" formula in Q modified to now reference new column P "T to do" rather than N:
Q5 = IF( $P5 > 0, COUNTIFS( $P$5:$P5, ">0" ), 0 )

Outstanding Training Courses
"Employee Code" formula in C16:C25 modified to now reference new column Q rather than P:
C16 = IFERROR( INDEX( $C$5:$C$13, MATCH( Q16, $Q$5:$Q$13, 0 ) ), "-" )

Modified result formulas in C16:M25 now a bit clunky through using OFFSET (from home base at row 4) to locate the correct employee/course combos ('cos I couldn't work out how to return a two cell range for T & the adjacent A using INDEX/MATCH!!!):
D16 = IF( AND( $C16 <> "-", D$4 = "T", IFERROR( SUM( OFFSET( D$4, MATCH( $C16, $C$5:$C$13, 0 ), 0 ) - OFFSET( D$4, MATCH( $C16, $C$5:$C$13, 0 ), 1 ) ) = 1, FALSE )), D$2, "-" )

Check count calc. of courses to be completed by employees shifted from N to P to align with the helper calc. in the Data Table.

I think I've got it this time.

 

[DHayes]

Good day Col Delane,
Thank you working perfect. Appreciated

 

[Col Delane]

No worries.