Help with Filtering data and matching two data sets?

[masai_chadi]

Hello All

I have two sets of data:

- list of Jobs with one or many Competency requirements.
- list of Employees with their Competencies.

I would like to create a filter scheme that allows a user to enter
Job, and have Excel automatically match Employees with competencie
that are required by the Job.

For example,

Job 'Cashier - Cafeteria' requires two competencies from each employe
working on this Job. So, in this case, there are 4 employees with thes
competencies: Employee 4, Employee 7, Employee 8, Employee 9.

Similarly,

Job 'Cleaner' requires 9 competencies from each employee working o
this Job. In this case, there is only one employee with all
competencies: Employee 8.

The attached spreadsheet shows the data sets.

How would I achieve this? Thanks.

Masa

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45654

 

[acw]

Masai

I've got an answer that entails the creation of a User
Defined Function. The function is

Function IsVisible(x) As Boolean
IsVisible = True
If Rows(x.Row).Hidden = True Then IsVisible = False
End Function

With this function available, enter the formula =isvisible
(B2) in cell C2 of sheet OHR Job Quals. Copy down as
required. Use the Data, Filter, autofilter on columns A
and B.

In sheet Emp Quals, enter the heading Match in C1, then
enter the formula in C2 and copy down. Again generate an
autofilter on columns A:C inclusive.

Using the OHR sheet, select the Cashier - Cafeteria from
the Job name in column A. 2 entries appear. Then go to
sheet Emp Quals, and select True from the Match column. 4
entries appear. Similarly for the other scenario you
mentioned, but you will have to reselect the match for
each change in the Job name.

HTH

Tony

 

[masai_chadi]

Hello Tony

Thank you for the solution. Just one question for clarification:

-You mentioned that "In sheet Emp Quals, enter the heading Match in C1
then enter the formula in C2 and copy down.". Do you mean the sam
function as entered on OHR Job Quals sheet, ie. =isvisible(b2) o
'=isvisible('OHR Job Quals'!B2)'?

When I enter either of these formula I get wrong matches.

I have uploaded the sample file with the formula so that you can se
what I am doing wrong.

Thanks

Masa

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46042