Log In
Register
Home
New Member
April 9, 2022
Offline
Hello -
In one cell I have a list of comma separated options that I need to validate against a fixed set of choices. The cell can contain more than one of the given choices. Thank you so much for your help!
July 16, 2010
Offline
Hi Katrina,
Excel currently can't work with multiple values in a cell and compare them to cells with individual values. Therefore, I would load the data to Power Query and split the comma separated values into separate rows, then you can compare the two lists. In the Items sheet of the attached file the query finds that all values in the Standardized Audience ID are present in the Unique ID Options table. You can change the join type (see tutorial linked to above) to return the matches if you prefer.
Note: records in the first table with text e.g. 802|PIPR5 have been removed. If you wanted to keep these values, then you'd need to also split them by the pipe delimiter and discard the text - PIPR5.
Hoe that points you in the right direction.
Mynda
1 Guest(s)
