I need to look up data in one colum with multiple optins and put it against a user name

[YOLO]

I've been sent a database that has results of a survey, which asks users to nominate their skills. Their replies are all on one line in a column, seperated by a semi colon.
How can I look up this data and return it into individual columns on another sheet?
I tried doing a look up with multiple criteria but it only returned the first skill in a line. I changed the colon to a space, comma but no better result. I have a fromlua that does a count but not sure how to convert it to what I need.

The attached sheet shows what I need. This is going to be a data base for another program, so I just need 1 and zero, although other data might need the actual cell text so maybe options for that. The raw data table runs A1 to BF20 so has a lot of data. Quite a few columns have the same issue, multiple responses on one line that I need to convert to individual columns.

Thanks

 

[AlanSidman]

Using Power Query to split data and then unpivot the table and then create a pivot table. See attached.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Please tick any specialised roles you perform", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Please tick any specialised roles you perform", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Please tick any specialised roles you perform.1", "Please tick any specialised roles you perform.2", "Please tick any specialised roles you perform.3", "Please tick any specialised roles you perform.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Please tick any specialised roles you perform.1", type text}, {"Please tick any specialised roles you perform.2", type text}, {"Please tick any specialised roles you perform.3", type text}, {"Please tick any specialised roles you perform.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Transposed Table1" = Table.Transpose(#"Transposed Table"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table1",{{"Column1", Order.Ascending}})
in
    #"Sorted Rows"

 

[Nebu]

Hi:

Find the attached, using power query.

Thanks

 

[YOLO]

Is there a way to only use the one column in 'raw data', rather than putting in new columns and then reading off them?
I already have used text to columns but wanted to see if I can keep the single source column to be able to populate the seperate columns in 'specialised roles'

 

[Nebu]

Hi:

Not sure what you meant by one column in raw data, could you upload a sample?

Thanks

 

[YOLO]

Sorry - didn't explain that correctly. The column B in raw data has the skills that I need to split out to the multiple columns in Specialsed Roles. they are seperated by comma's. I was hoping that I could take those multiple skills from the orignal single column (as the data is imported from our database) and split them into seperate columns in Specialised roles tab. I already put the 1 and zero in, as an example of what I needed.

I am thinking that creating new columns in raw data to split the skills is the only <simple> answer?

 

[Nebu]

Hi:
Is this what you are looking for?

Thanks