Combining several lists in one Master

[Noob21]

Hi there,

For a project i would like to create an excel tool that collects and systemates the skills needed in an organization.
My idea is to create a worksheet for every member where the necessary skills are listed.

To get an overview over the organisation, i would additionally like to create one "master" worksheet, that collects/aggregates the items of all the other lists automaticly.

This list should be updating automatically, so if i add or delete a skill from a list, the master list should be updated.
There should be also no duplicates, skills that are requiered for more than one person, should appear only once on the master list


Example:

Person1: Skill A, B, D, G
Person2: Skill A, G
Person3: Skill A, E, F
Person4: Skill B, H, I

Organisation: Skill A, B, C, D, E, F, G, H, I


So far I couldnt find a way to realize it. If you have any idea it would be great!

Thank you for your support

[Pete_UK]

Welcome to the forum - I visited Switzerland on holiday a couple of weeks ago.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Noob21]

Hi Pete

Thank you for your quick reply!
I hope you enjoyed your time there

I attached you an example file to faciliate understanding my issue.

Best Regards
Tobias

[JeteMc]

I don't know how far along you are with this project, however putting each persons skills on a separate sheet seems to be over complicating the task. There are over 1M rows available on a sheet so that if the organization has 100k employees and each has 10 skills that will still fit on a sheet.
In the attached copy of the all individual skills are listed on the 'Organization' sheet along with the person utilizing them.
The formula that compiles the organizational list is: =IFERROR(IF(MATCH(0,INDEX(COUNTIF(E$1:E1,B$2:B$20),,),)>COUNTA(B$2:B$20),"",INDEX(B$2:B$20,MATCH(0,INDEX(COUNTIF(E$1:E1,B$2:B$20),,),))),"")
Let us know if you have any questions.