Help need - IF AND Appropriate?

[dyelow]

Hello all,

I need some help with an issue I’m currently facing (apologies if it’s straightforward to some just can’t seem to figure it out).

So I have a spreadsheet of 1000’s of entries for users, where I have determine their compliance for training. All the users are registered to either a virtual training session or a face to face session, so each user will have at least 2 entries (some may have more than this as they may have more than one face to face session). If they have completed either of these (i.e. either their classroom session/s or their virtual training), then I can mark them as compliance.

Currently I get this spreadsheet with separate compliance reasons for either the virtual of f2f (in separate columns) but the report doesn’t not show them as compliant if they have completed either (as it looks at all the modules on their profile)

Anywho, is there a way that I can check if a name appears more than once, this can be cross referenced against all the data set and also if this user has a yes against any of their compliance, they are marked as yes. I’ve played around with IF AND formulas but can’t seem to get the right output

Can anyone help, please?

 

[jasonb75]

Best guess without seeing a visual representation of the layout would be something using COUNTIFS. In theory, something similar to this.

=IF(COUNTIFS(range1, criteria1, range2, criteria2...)>=2,"Compliant","Not Compliant")

A formula using AND only works if everything meets the criteria, it would not be useful for identifying a small number of matches in a large number of records.

 

[DaveMacdonald]

See reply. I wasn't sure it would notify you if I had done it wrong haha.

 

[DaveMacdonald]

Hello all,

I need some help with an issue I’m currently facing (apologies if it’s straightforward to some just can’t seem to figure it out).

So I have a spreadsheet of 1000’s of entries for users, where I have determine their compliance for training. All the users are registered to either a virtual training session or a face to face session, so each user will have at least 2 entries (some may have more than this as they may have more than one face to face session). If they have completed either of these (i.e. either their classroom session/s or their virtual training), then I can mark them as compliance.

Currently I get this spreadsheet with separate compliance reasons for either the virtual of f2f (in separate columns) but the report doesn’t not show them as compliant if they have completed either (as it looks at all the modules on their profile)

Anywho, is there a way that I can check if a name appears more than once, this can be cross referenced against all the data set and also if this user has a yes against any of their compliance, they are marked as yes. I’ve played around with IF AND formulas but can’t seem to get the right output

Can anyone help, please?

*dyelow,

You can identify duplicates pretty easily using Conditional Formatting if that was your question. Check this article for help with that specifically. How to Highlight Duplicates in Excel (6 Easy Ways) - ExcelDemy

You can also just sort the name column alphabetically. I'm not sure I completely understand what you're trying to accomplish though. If you're trying to check if an individual has either F2F or virtual training, and either of them completed means that the person is compliant, you can use an OR formula, depending on how the information is presented. is the lack of training represented by a blank cell under the column heading? or does it say "incomplete" or "no" or some such? There's absolutely a way to help, I just need more information. =IF(OR(F2Fcell="complete",virtualcell="complete),"compliant","not compliant").

If you're still interested in help just respond and I'll do my best! I've gotten a ton of help from the folks on this forum so I'm more than happy to give back!