Cross Referencing With More Than One Variable?

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