Using Excel to compile these data points - Is it possible?

[nexus]

I wanted your advice on how you think I should go about this and if you think an end result is possible.

There are currently around 150 employees in my department scattered around 4 offices around the world. The majority of employees in our department speaks at least 2 languages and has a varied set of knowledge when it comes to specific regions around the world.

We frequently have cases where an analyst needs help from another analyst who speaks one language and/or has knowledge of a specific region (East Asia, Middle East, South America etc).

I want to create an Excel document (Or by using another software) to compile the following data points that I gather:

1) Office Location
2) Employee Name
3) Day of Hire
4) Shift Hours
5) Days Off
6) Language Skills - English / Spanish / French etc.
7) Region Skills - United States / South America / Eastern Asia etc.

So an analyst can search for "Spanish language skills" and/or "East Asia knowledge" and it'll show the analysts with those skills.

I've been doing research online and I've been told that Excel does not handle many-to-one relationships all that well and that my easiest option would probably be something simple like making a table with headers out of the data and just filtering it through there.

How do you guys think I should go about this?

 

[initialize]

I would recommend the same. Use advanceFilter and set the criteria. U can do advancefilter in vba too

 

[FDibbins]

I would try something like this. I know that it means multiple entries for the same EE, but it will produce a filterable/searchable table...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

4​

[/td][td]Employee Name[/td][td]Office Location[/td][td]Day of Hire[/td][td]Shift Hours[/td][td]Days Off[/td][td]Language Skills[/td][td]Region Skills[/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td]US[/td][td][/td][td][/td][td][/td][td]eng[/td][td]US[/td][/tr]

[tr][td]

6​

[/td][td]aa[/td][td]US[/td][td][/td][td][/td][td][/td][td]Sp[/td][td]S A[/td][/tr]

[tr][td]

7​

[/td][td]aa[/td][td]US[/td][td][/td][td][/td][td][/td][td]Fr[/td][td]E A[/td][/tr]

[tr][td]

8​

[/td][td]bb[/td][td]S A[/td][td][/td][td][/td][td][/td][td]eng[/td][td]US[/td][/tr]

[tr][td]

9​

[/td][td]bb[/td][td]S A[/td][td][/td][td][/td][td][/td][td]Sp[/td][td]S A[/td][/tr]

[tr][td]

10​

[/td][td]cc[/td][td]S A[/td][td][/td][td][/td][td][/td][td]Fr[/td][td]S A[/td][/tr]

[tr][td]

11​

[/td][td]dd[/td][td]E A[/td][td][/td][td][/td][td][/td][td]eng[/td][td]US[/td][/tr]

[tr][td]

12​

[/td][td]dd[/td][td]E A[/td][td][/td][td][/td][td][/td][td]Sp[/td][td]E A[/td][/tr]

[tr][td]

13​

[/td][td]dd[/td][td]E A[/td][td][/td][td][/td][td][/td][td]Fr[/td][td]S A[/td][/tr]

[tr][td]

14​

[/td][td]dd[/td][td]E A[/td][td][/td][td][/td][td][/td][td]Ita[/td][td]EU[/td][/tr]
[/table]
If you apply filters Home tab/Editing/Filter and Sort/Filter, you will get a drop-down - click on that and you can check various entries, and you can do that for as many columns as you want

 

[nexus]

Thank you for your help. However, how do I organize some employees who speak 3, 4, and sometimes 5 languages? Also employees who have multiple region knowledge. I am thinking when I do the survey that I will ask for the top 3 or top 5 regions that the employee is proficient in.

 

[mikerickson]

Rather than having one "Language skills" column and having the hassle of "English" matches "English, French" (and "french,english" means exactly the same as "English, French") it might be easier to have one column for each language and you enter yes/no if that employee has that skill.

 

[nexus]

Rather than having one "Language skills" column and having the hassle of "English" matches "English, French" (and "french,english" means exactly the same as "English, French") it might be easier to have one column for each language and you enter yes/no if that employee has that skill.

So there will be around 10 columns for each language skill along with 10 or so columns for each region around the world. Is there a way to be able to compile all the language columns so its easier to filter or is the easiest just scanning the column for the specific language you want and choosing "Yes" for that filter?

 

[mikerickson]

The Yes for each filter would be the way to go. The built in AutoFilter feature can handle mixed columns, but the user has to have some experience. Filter each column is more work, but more intuitive, it can be done right the first time by almost anyone.

 

[FDibbins]

Mike, I was initially thinking of a column for each skill, but hoped we could do it all in 1 - your suggestion confirms that multi-columns is probably the way to go

 

[mikerickson]

I've written sheet like that and they can look ugly. Long columns of empty cells. The one suggestion I would add is to use the Rotate Text for the headers to help narrow the column.

 

[FDibbins]

Yup, the other option is to use 1 column for the skill, but then you need to repeat the name multiple (and possibly multiple) times for each other factor.

So with 5 languages and 3 skills, that means repeat the name 15 times - also a pain