Pivot table - multiple entries per row

[Tashat]

Hello wonderful Excel people

Can someone help me with a pivot table issue please? (data below is a short excerpt of test data) - I have many more lines. Most people in the list speak more than one language ( separated by | ). I want to be able to pull a pivot table which shows all the speakers of each language. So for example, I want each language in the column down the side and then all the names of the people who speak that language. Therefore, some people's name will appear 2 or 3 times in the pivot table. Is this possible? Can anyone advise how I could do this?

I may also want to pull in region details at some point, but one set at a time. Thanks in advance

Book1
	A	B	C
1	Name	Region	Language
2	Harkishan	Yorkshire & The Humber	Swahili
3	Bushra	Yorkshire & The Humber	Punjabi | Urdu
4	Nusrat	Yorkshire & The Humber	Gujarati | Other
5	Neda	Cheshire & Liverpool	Punjabi | Urdu
6	Gloria	Cheshire & Liverpool	Bengali
7	Ashleigh	West Midlands	Not Applicable
8	Oyekanmi	West Midlands	Arabic
9	Subhash	West Midlands	Cantonese | Mandarin | Other
10	Zeid	West Midlands	Yoruba | Other
11	Anwaar	West Midlands	Not Applicable
12	Catherine	West Midlands	Arabic
13	Sumia	West Midlands	Punjabi | Urdu
14	Haseeb	West Midlands	Punjabi | Urdu
15	Sanjay	West Midlands	Gujarati | Punjabi | Urdu | Other
16	Athman	West Midlands	Gujarati | Swahili | Urdu
17	Niaz	West Midlands	Bengali
18	Jyoti	West Midlands	Punjabi | Urdu | Other
19	Iftikhar	East Midlands	Gujarati | Urdu | Other
20	Guixian	East Midlands	Mandarin
21	Lolita	East Midlands	Arabic | Other
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C14:C21	Expression	=$I14="Vacancy"	text	NO
C14:C21	Expression	=$I14="Resigned"	text	NO
C2:C13	Expression	=$I2="Vacancy"	text	NO
C2:C13	Expression	=$I2="Resigned"	text	NO

 

[Tashat]

Hi - is anyone able to help me with this issue please? I really need a pivot table which can show all the languages once and then list the names of each person who can speak a given language. It is difficult because there is only one entry for each person and within one cell, there could be 3 or 4 language they speak, separated by a | ... I'm stumped by this one.

 

[sandy666]

you mean like this?

Name	Region	Language
Lolita	East Midlands	Arabic
Oyekanmi	West Midlands	Arabic
Catherine	West Midlands	Arabic
Gloria	Cheshire & Liverpool	Bengali
Niaz	West Midlands	Bengali
Subhash	West Midlands	Cantonese
Iftikhar	East Midlands	Gujarati
Sanjay	West Midlands	Gujarati
Nusrat	Yorkshire & The Humber	Gujarati
Athman	West Midlands	Gujarati
Subhash	West Midlands	Mandarin
Guixian	East Midlands	Mandarin
Anwaar	West Midlands	Not Applicable
Ashleigh	West Midlands	Not Applicable
Sanjay	West Midlands	Other
Lolita	East Midlands	Other
Jyoti	West Midlands	Other
Nusrat	Yorkshire & The Humber	Other
Iftikhar	East Midlands	Other
Subhash	West Midlands	Other
Zeid	West Midlands	Other
Neda	Cheshire & Liverpool	Punjabi
Bushra	Yorkshire & The Humber	Punjabi
Jyoti	West Midlands	Punjabi
Haseeb	West Midlands	Punjabi
Sumia	West Midlands	Punjabi
Sanjay	West Midlands	Punjabi
Athman	West Midlands	Swahili
Harkishan	Yorkshire & The Humber	Swahili
Neda	Cheshire & Liverpool	Urdu
Iftikhar	East Midlands	Urdu
Sumia	West Midlands	Urdu
Bushra	Yorkshire & The Humber	Urdu
Jyoti	West Midlands	Urdu
Sanjay	West Midlands	Urdu
Haseeb	West Midlands	Urdu
Athman	West Midlands	Urdu
Zeid	West Midlands	Yoruba

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Language", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Language"),
    Sort = Table.Sort(Split,{{"Language", Order.Ascending}})
in
    Sort

 

[sandy666]

or like this

Language	Names
Arabic	Catherine, Lolita, Oyekanmi
Bengali	Gloria, Niaz
Cantonese	Subhash
Gujarati	Athman, Iftikhar, Nusrat, Sanjay
Mandarin	Guixian, Subhash
Not Applicable	Anwaar, Ashleigh
Other	Iftikhar, Jyoti, Lolita, Nusrat, Sanjay, Subhash, Zeid
Punjabi	Bushra, Haseeb, Jyoti, Neda, Sanjay, Sumia
Swahili	Athman, Harkishan
Urdu	Athman, Bushra, Haseeb, Iftikhar, Jyoti, Neda, Sanjay, Sumia
Yoruba	Zeid

 

[Tashat]

or like this

Language	Names
Arabic	Catherine, Lolita, Oyekanmi
Bengali	Gloria, Niaz
Cantonese	Subhash
Gujarati	Athman, Iftikhar, Nusrat, Sanjay
Mandarin	Guixian, Subhash
Not Applicable	Anwaar, Ashleigh
Other	Iftikhar, Jyoti, Lolita, Nusrat, Sanjay, Subhash, Zeid
Punjabi	Bushra, Haseeb, Jyoti, Neda, Sanjay, Sumia
Swahili	Athman, Harkishan
Urdu	Athman, Bushra, Haseeb, Iftikhar, Jyoti, Neda, Sanjay, Sumia
Yoruba	Zeid

Yes this one. Thank you.

 

[sandy666]

sure, so use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Language", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Language"),
    Sort = Table.Sort(Split,{{"Name", Order.Ascending}}),
    Group = Table.Group(Sort, {"Language"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Names", each [Count][Name]),
    Extract = Table.TransformColumns(List, {"Names", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Srt = Table.Sort(Extract,{{"Language", Order.Ascending}})
in
    Srt

 

[Tashat]

sure, so use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Language", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Language"),
    Sort = Table.Sort(Split,{{"Name", Order.Ascending}}),
    Group = Table.Group(Sort, {"Language"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Names", each [Count][Name]),
    Extract = Table.TransformColumns(List, {"Names", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Srt = Table.Sort(Extract,{{"Language", Order.Ascending}})
in
    Srt

Thank you so much for this. Are you able to explain to me where I need to paste this query/language please? I'm searching for Power Query - not having much luck

 

[sandy666]

XL365 contain Power Query, this is built-in there
so
create blank query

hope that way is similar to XL365
then go to Advanced Editor and replace all there with code from the post
remember that the name of the source table should be the same as in the code (here: Table1), if not change to proper name in the code.
then Ok, Close&Load

 

[Tashat]

sure, so use this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Language", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Language"),
    Sort = Table.Sort(Split,{{"Name", Order.Ascending}}),
    Group = Table.Group(Sort, {"Language"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Names", each [Count][Name]),
    Extract = Table.TransformColumns(List, {"Names", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Srt = Table.Sort(Extract,{{"Language", Order.Ascending}})
in
    Srt

Thank you so much for this. Are you able to explain to me where I need to paste this query/language please? I'm searching for Power Query - not having much luck

XL365 contain Power Query, this is built-in there
so
create blank query
View attachment 27023
hope that way is similar to XL365
then go to Advanced Editor and replace all there with code from the post
remember that the name of the source table should be the same as in the code (here: Table1), if not change to proper name in the code.
then Ok, Close&Load

Wow - thank you. I'm getting much closer now, but I'm now getting an error which says:

"[Expression.Error] The column 'Language' of the table wasn't found."

I have named the table - "table1" by naming the range of the source data. Is that correct? Should I include the row headers in the named range? Thank you so much for your support with this. It's great I'm learning a whole new area of Excel.

 

[sandy666]

the best way is select whole range and hit Ctrl+T, it will create excel table
about error: check if the name of column Language is real Language but not language. Power Query is case sensitive !!!