Checking if employees have completed training? Vlookup, countifs, ... ?

E

esgca2010

New Member
Joined
Jul 29, 2010
Messages
40
Hello,

Pardon my question if it has been answered before. It's a rush job and need your help.

Columns A through C are employees that have completed training. Columns E through G are all employees. Columns I through K should show results.

Question: Compare the employees who completed the training and show the remaining employees who have not completed the training in Columns I through K.

ABCDEFGHIJK
1Training CompletedAll Employee NamesResults
2First NameLast NameLOCFNLNCodesEmp FNEmp LNLocation Code
3TeriBinga5LornaKling4
4TheresaCalifano1SeanWillis1
5BobAmbrose2ColleenAbel3
6TeriBinga5
7FrankCulbert2
8KristenDeVinney2
8TheresaCalifano1
10BarryBally5
Again, thank you so much for all of your help!

Respectfully,

Elaine
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
What version of Excel are you using? It would help if you updated your profile to show us.
 
Upvote 0
another way
use helper columns to textjoin the details both the list and the completed then use this formula

=FILTER(F4:H11,NOT(ISNUMBER(XMATCH(I4:I11,D4:D6))))
1727163934152.png
 
Upvote 0
Building on @ExcelNewbie2020 's excellent answer, you can do it without additional helper columns if you wish.

Book1
ABCDEFGHIJK
2Training CompletedAll Employee NamesResults
3First NameLast NameLOCFNLNCodesEmp FNEmp LNLocation Code
4TeriBinga5LornaKling4LornaKling4
5TheresaCalifano1SeanWillis1SeanWillis1
6BobAmbrose2ColleenAbel3ColleenAbel3
7TeriBinga5FrankCulbert2
8FrankCulbert2KristenDeVinney2
9KristenDeVinney2BarryBally5
10TheresaCalifano1
11BarryBally5
Sheet1
Cell Formulas
RangeFormula
I4:K9I4=LET(_a,BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))), _b,BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))), FILTER(E4:G11,NOT(ISNUMBER(XMATCH(_a,_b)))))
Dynamic array formulas.
 
Upvote 0
Solution
myall_blues said:
Building on @ExcelNewbie2020 's excellent answer, you can do it without additional helper columns if you wish.

Book1
ABCDEFGHIJK
2Training CompletedAll Employee NamesResults
3First NameLast NameLOCFNLNCodesEmp FNEmp LNLocation Code
4TeriBinga5LornaKling4LornaKling4
5TheresaCalifano1SeanWillis1SeanWillis1
6BobAmbrose2ColleenAbel3ColleenAbel3
7TeriBinga5FrankCulbert2
8FrankCulbert2KristenDeVinney2
9KristenDeVinney2BarryBally5
10TheresaCalifano1
11BarryBally5
Sheet1
Cell Formulas
RangeFormula
I4:K9I4=LET(_a,BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))), _b,BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))), FILTER(E4:G11,NOT(ISNUMBER(XMATCH(_a,_b)))))
Dynamic array formulas.
Click to expand...
Hello,

Your formula works. Can you break it down for me so I can understand the formula? My brain is fried at this hour.

Thank you! You are the BEST!

Respectfully,

Elaine
 
Upvote 0
Hi Elaine

If I rearrange the formula to remove the LET, which is really just a way of simplifying complex formulas, it will look like this:
Excel Formula: 
=FILTER(E4:G11,NOT(ISNUMBER(XMATCH(BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))),BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array)))))))

BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))) takes the range specified (A4:C11) and calls it 'array' inside the LAMBDA function, then processes that array through TEXTJOIN on a row by row basis (because of the BYROW). The TEXTJOIN just puts the three columns together with no spaces in between. Doing this creates three virtual 'rows' of data:
TeriBinga5
TheresaCalifano1
BobAmbrose2

BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))) does the same thing with E4:G11

XMATCH between those two arrays will give a number for the location of any matches in the second array with the name in the first array, so it gives 1 for TeriBinga5 and 2 for TheresaCalifano1. All the rest are not found so give an #N/A error. NOT(ISNUMBER(... reverses that result because you want people that have not had training. Now use all that to filter the array E4:G11, where it includes all the things returned by the list we have derived.

Finally, put it inside a LET statement to make it slightly more readable.
Assign BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))) to the variable _a
Assign BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))) to the variable _b

Substitute those variables into the formula
Excel Formula: 
=LET(_a,BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))),
_b,BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))),
FILTER(E4:G11,NOT(ISNUMBER(XMATCH(_a,_b)))))

Hope this helps.
 
Upvote 0
myall_blues said:
Hi Elaine

If I rearrange the formula to remove the LET, which is really just a way of simplifying complex formulas, it will look like this:
Excel Formula: 
=FILTER(E4:G11,NOT(ISNUMBER(XMATCH(BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))),BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array)))))))

BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))) takes the range specified (A4:C11) and calls it 'array' inside the LAMBDA function, then processes that array through TEXTJOIN on a row by row basis (because of the BYROW). The TEXTJOIN just puts the three columns together with no spaces in between. Doing this creates three virtual 'rows' of data:
TeriBinga5
TheresaCalifano1
BobAmbrose2

BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))) does the same thing with E4:G11

XMATCH between those two arrays will give a number for the location of any matches in the second array with the name in the first array, so it gives 1 for TeriBinga5 and 2 for TheresaCalifano1. All the rest are not found so give an #N/A error. NOT(ISNUMBER(... reverses that result because you want people that have not had training. Now use all that to filter the array E4:G11, where it includes all the things returned by the list we have derived.

Finally, put it inside a LET statement to make it slightly more readable.
Assign BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))) to the variable _a
Assign BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))) to the variable _b

Substitute those variables into the formula
Excel Formula: 
=LET(_a,BYROW(E4:G11,LAMBDA(array,TEXTJOIN("",FALSE,array))),
_b,BYROW(A4:C11,LAMBDA(array,TEXTJOIN("",FALSE,array))),
FILTER(E4:G11,NOT(ISNUMBER(XMATCH(_a,_b)))))

Hope this helps.
Click to expand...
All I can say is a sincere thank you for your help! You are the best and truly appreciate your support.

Humbly,

Elaine
 
Upvote 0
You're very welcome. I'm glad we could get it sorted for you.
 
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,223,869
Messages
6,175,087
Members
452,611
Latest member
bls2024

Share this page

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