Multiple data import and sort

[dicky5150]

Hi, as part of our organisations response to Corona Virus, we're trying to assess the skills we have outside of our regular roles and which we would be able to deploy to support other important functions. As part if this, everyone has been asked to fill in the XL form below. I've now been tasked with trying to collate the data, which I've been successful in importing through a directory tool and dynamic input.

What I'm finding harder though is;

1). stack the data so that each only the "X" comes in and each subsequent sheet appears next to the initial header

2). Parse out the file name containing each persons name  so that it becomes part of the data.

Ideally I'd like to end up with a set of skills down one side, names along the top and each of the X's corresponding to skills correctly imported.

Does anyone have any idea of where to start ?!!?

Regards and much love to you at a difficult time for us all,

Dicky

[echuong1]

Are you able to post one of the excel files to use as an example? 

I'm thinking this can be achieved using the multi-row formula, parsing, and filters.

[dicky5150]

Happy to !

[AbhilashR]

@dicky5150 - I suspect I may not have understood your ask (specifically the stacked bit). But nevertheless take a look at the attached solution. It reads multiple files (as you mentioned), and brings in filepath and parses out the filename you were looking for.

[echuong1 · Accepted solution]

I believe this is what you're looking for - essentially I have the skills in one column and the person's name as the column headers with X in the cells below if the skill was marked off. I also included some cleansing to get everything in the same format if they used a capital or lowercase X. 

Your input tool will just need to point to the folder with the files. In place of the file name, use wildcards. In my example, I'm looking for any excel file with "ISO Skills_example"

[dicky5150]

Many thanks Erica, that really helps me !

Regards

Dicky