Cross Referencing With More Than One Variable?

Does 'fully compliant' just mean, they have taken every course? Also, I assume you know the number of courses?


Also, on one tab you have Employee - on the Other Location - is there a table somewhere we are missing that connects people and the locations?

Hi Harry,

If your data was arranged better you could do a Pivot Table to get an answer. I've created a macro to change these types of "Crosstab Tables" into better Excel type tables. I've included my macro in the attached and run it on Sheet 1 data, which gets it to Sheet 2 table. Then a Pivot is done on the Sheet 2 data and your answer is there. I think this is what you want.

Just as an alternative.

I think you could:
** Add a column to 'Testing Method 1' to combining Columns A, B and C (to create a kind of key).
** Add a column to check "does this row indicate a unique, completed course for this employee?"

Please Login or Register  to view this content.

**You will need a Staff-To-Location table (which I made up and added) on sheet 3.

**After that you, add a 'Score' table to count how many courses were completed for each employee on the 'Testing Method' tab - finally you deem them 'fully compliant if this score equals 13.

Back on the first tab, you just sumif() on this table (see attachment).


Just adding this as an alternative example, I like pivots like Marvin's post

Hi both,

Thank you for your suggestions so far!

Marvin - I ran your macro and entered '3' for the number of fixed columns. The first part worked and it restructured my columns, but I don't seem to get the 'Sheet 2 Table' and the 'Value' column is empty. I've attached what my data looks like after running the macro (saved in a different format due to size limit). I've looked at the macro workbook that you sent and I've never really used pivot tables before but it looks like they might come in handy. However, am I right in thinking that it the pivot table is only useful to find out how compliant each member of staff is compared to how compliant they should be (completed all 13 courses), as oppose to how many staff are compliant enough to work at say 'Location 1' in the first worksheet?

For example, for 1 member of staff to be compliant enough to work at Location 1 they need to have at least completed courses 2, 3, 4, 5, 6, 7, 8, 10 ,11, 12 and 13. Then they would count as 1 person being compliant. So what I need to do is find out, out of all the staff in the data, how many have at least completed courses 2,3,4,5,6,7,8,10,11,12 and 13, and then total that in the first worksheet in cell 'O2'. Then I need to do the same for Location 2, in which a person must have completed courses 1,2,4,5,6,7,8,10,11,12 and 13 to be compliant, then total this and enter in cell 'O3'.

Sorry, I'm not very good with pivot tables. Would I be able to display this information using various filters? Thanks!

GeneralDissarray - Thanks also for providing your method. I usually do the same thing with the 'unique key' (but I use =COUNTIF to check the 'uniqueness'). There shouldn't be any duplicate records. It must have been a manual error when I was setting up the example data!

I'm looking at the Staff->Location table and it looks good but I'm not sure if I have the same problem as I had with Marvin's suggestion? Is your table giving me how many people have completed enough courses in total (not which courses specifically) and then matching this to like a number figure for that location, as oppose to the specific group of courses someone needs to complete to be compliant for that location?

Don't worry if this is seeming unlikely to find a solution to. I did it manually last time, it just takes a while to do that's all .

Thanks again!

Harry

Originally Posted by Harry Sowden

Hi both,
Is your table giving me how many people have completed enough courses in total (not which courses specifically) and then matching this to like a number figure for that location, as oppose to the specific group of courses someone needs to complete to be compliant for that location?.

Yes: it is.

It would be possible to do what you explain (determine who is compliant for a location). But, we would then need to know what each location requires for compliance.

I think that would actually be easier to deal with, but can you get a table with that information?

Hi,

Sorry for maybe not been clear enough originally. In the original spreadsheet (that I attached to the first post) - in the first worksheet ('No. of Comp Staff by Location') - the chart there. For someone to be fully compliant for that location they need to have completed all the courses with a 'yes' in that cell. Each 'yes' means that it's mandatory that that course is complete as well as all the other yes's in that row in order to be compliant for to work at that location.

I'd guess there is a way of using multiple IFs and LOOKUPs?

Hope this makes sense? Let me know if the table needs reformatting and I can work something out to make it easier to read.

Many thanks

EDIT: Original attachment had a formula error -see FIXED copy currently attached.

Ok. I think I got it for you - I had to change the file format to upload it (.xlsx is a compressed format) - it was too large as a .xls file


Here's what I did.

I Created a tab call "EMPLOYEE RESULTS CODED" - that list each employee once. Next to the Employee ID: you will see 13 columns of 1's or 0's - this tells you which courses the employee has passed. 1 = have completed this course, 0 = have not. After those columns you will see a bunch of "TRUE" & "FALSE" values for each location (for the next 83 columns).

That tells you if a given employee is compliant for a given location.



Back on your "No. of Comp Staff by Location" tab - you'll see the counts for each location (counts the "trues" from the other tab).

Please have a look, this one took some noodling

Thank you- glad to be of help This was an interesting problem.