How to obtain unique values from a multivalued cells in a column?

yabi100

Board Regular
Joined
Aug 10, 2013
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
I have a data base of films, one field name is cast and some films has more than one value in one cell. Let say film A has Martin Speer|Russ Grieve|John Steadman|James Whitworth|Virginia Vincent as cast and film B has Lindsay Lohan|Gus Van Sant|James Deen|Nolan Gerard Funk|Amanda Brooks as cast all stored in one cell and all are separated by "|". This database has 3000+ records and some casters appear in more than one film. Now I want to obtain a unique value of casters for this data base. There are some resources in the web talking about this issue but I can't figure out which command and which technique I should start with to solve the problem. If it helps, I have to add, I need this unique list to use in my pivot table to produce some reports based on genre and which casters participated in production year of let say between 1960 till 1970. Also I want to know that if it is possible to do it inside power query using "|" as delimiter?

Any help is highly appreciated.
 

Attachments

  • unique vakue of multivalued cells in pt-parscodrs malihe project.JPG
    unique vakue of multivalued cells in pt-parscodrs malihe project.JPG
    240.1 KB · Views: 16

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,393
Messages
6,171,839
Members
452,427
Latest member
samk379

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top