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]

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 !!!

Thank you so much! I've got it to work. That is so amazing and started me off on a new path of learning in Excel. Thank you.

Can I be super cheeky and ask if there is a way to make the Names appear in a list with each name on a new line within the single cell against each language? I'll have to teach myself all the syntax in this. Thank you.

 

[sandy666]

you mean like this?

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

 

[Tashat]

you mean like this?

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

Hello apologies for the delay

No I meant like this...are you able to help?

Book1
	A	B
1	Language	Names
2	Akan	Gloria Asumang Kwame Nuako Obed Ababio Solomon Asiedu
3	Arabic	Abbas Zayni Amier Amily Amira Akshar Asif Ali Chuka Dieobi Intisar Alqsar Lolita Hardy Maged Musaeed Maria Nader Neda Al-Yafeai Ramie El-Nahas Salima Awad Sumia Afif
4	Bengali	Alamgir Miah Fatima Munim Niamat Shaer Niaz Jaigirdar Phul Meah
5	Cantonese	Cheok Teng Lok Cheok Wun Lok Cissie Tsang Jinyu Shan Lai Kam Ng Lu Cai Wai Cheung
Sheet1

 

[sandy666]

// Table1
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")
in
    Split

then Close&Load - Load To - Only create connection - Load
and then Insert - Pivot Table and use Query table as data source

Language	Name
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]

// Table1
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")
in
    Split

then Close&Load - Load To - Only create connection - Load
and then Insert - Pivot Table and use Query table as data source

Language	Name
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

That's absolutely brilliant! Thank you! It's working perfectly.

 

[sandy666]

You are welcome

​