Share via

How to create new columns from unique values from existing columns and fill based on values (change format of data)

Anonymous
2024-01-16T19:04:55+00:00

I currently have data in this format:

Name Title
Tom Title A, Title B
Sally Title A, Title C, Title D
Ben Title C

I technically also have it in the format of one title per line with duplicate names (ex. Tom | Title A, Tom | Title B, Sally | Title A, etc.) if that would be easier to work with. I ultimately want unduplicated names though.

I want to get it into this format:

Name Title A Title B Title C Title D
Tom 1 1 0 0
Sally 1 0 1 1
Ben 0 0 1 0

How can I do that?

I previously added separate columns for each title and used this formula: =IF(ISNUMBER(SEARCH(E$2,$D3)), 1, 0) where E2 contained the comma-separated list of titles and D3 contained the column header with the specific title (for example, E2 would be "Title A, Title B" for Tom and D3 would be "Title A").

However, some of the titles contain overlapping text (for example, "Manager" and "Senior Manager" or "Nurse" and "Nurse Practitioner"), so the less specific title is also counting more specific titles that contain the same text. I need them to be separate.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-01-17T00:01:23+00:00

    Hi,

    This Power Query M code works

    let 

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 

#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Title", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Title"), 

#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Title", Text.Trim, type text}}), 

#"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Title", "Title - Copy"), 

#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Title]), "Title", "Title - Copy", List.Count)

    in 

    #"Pivoted Column"

    Hope this helps.

    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-01-16T19:45:04+00:00

    In E3:

    =IF(ISNUMBER(SEARCH(", "&E$2&", ", ", "&$D3&", ")), 1, 0)
    0 comments