Splitting and re-combining two columns with comma-separated values
Apologies in advance if the formatting comes out all weird. And apologies if this has been asked and answered - at a glance I didn't see anything that QUITE matches my problem, but maybe I just need to dig more. I'm using Excel 365.
I have a table that looks like this:
| ID | Fruits | Codes |
|---|---|---|
| Unique1 | Apples , Apples , Bananas | A123 , A123 , B456 |
| Unique2 | Apples , Apples , Cherries | A123 , A123 , C789 |
| Unique3 | Apples , Apples , Bananas | A123 , A123 , B456 |
| Unique4 | Apples , Apples , Apples , Bananas | A123 , A123 , A123 , B456 |
And the output I'm looking for would be this, where the nth element of the Fruits column and the nth element of the Codes column are aligned with their corresponding unique ID:
| ID | Fruits | Codes |
|---|---|---|
| Unique1 | Apples | A123 |
| Unique1 | Apples | A123 |
| Unique1 | Bananas | B456 |
| Unique2 | Apples | A123 |
| Unique2 | Apples | A123 |
| Unique2 | Cherries | C789 |
| Unique3 | Apples | A123 |
| Unique3 | Apples | A123 |
| Unique3 | Bananas | B456 |
| Unique4 | Apples | A123 |
| Unique4 | Apples | A123 |
| Unique4 | Apples | A123 |
| Unique4 | Bananas | B456 |
What I'm ultimately looking for: each Fruit SHOULD have the same corresponding code (Apples should always be A123, Bananas always B456, Cherries always C789). I'm trying to identify any instances where this is not the case.
For example, if there's an ID where maybe an Apple has A125 instead of A123. Or an instance where an ID is missing a code, like if 'Apples , Bananas , Cherries' has code 'A123 , C789' (no code for the Bananas)
I'm sure Power Query can do it, I'm just not well-versed in how Power Query works. I can get it to separate each column, but am not sure how to then go back and pair each column Fruits_# with column Codes_#.
[link] [comments]
Want to read more?
Check out the full article on the original site