I have a data set that has an ID and a name for each object. One the same row I have a number of different variables associated with each object. Several of the objects have more than one variable attached to them. However, the source data puts all the variables for each object in one cell. When I use text-to-columns to delimit the data, I end up with a mix of variables in each new column. See table 1 below. What I’d like to do is group all the same variables associated with each object into a column for each. Table 2 is an example of that. I manually made the change. But since I’m working with a much bigger dataset, I’m hoping for help with either a formula, or, more likely some VBA code. Automation as a result of the code would be great in case there are changes to the data.
For context my purpose is to be able to filter each object by single variable.
Yes, the data I am working with is primarily text. Certainly, the variables are all text. The tables are formatted as tables. (If that makes sense! )
Thank you to all creative thinkers and problem solvers.
| Table 1 | |||||
| ID | Name | Rule1 | Rule2 | Rule3 | Rule4 |
| 1 | Monday | Blue | Yellow | ||
| 2 | Tuesday | Blue | Yellow | Red | |
| 3 | Wednesday | Blue | Yellow | Red | |
| 4 | Thursday | Blue | Yellow | Red | |
| 5 | Friday | Blue | Yellow | Red | |
| 6 | Saturday | Red | Yellow | ||
| 7 | Sunday | Red | Blue | Yellow | |
| 8 | January | Red | Blue | Yellow | |
| 9 | February | Red | Blue | Green | |
| 10 | March | Red | Blue | Green | |
| 11 | April | Red | Blue | Green | |
| 12 | May | Red | Blue | Green | |
| 13 | June | Yellow | Red | Blue | Green |
| 14 | July | Yellow | Red | Green | |
| 15 | August | Yellow | Red | Green | |
| 16 | September | Green | Red | ||
| 17 | October | Green | |||
| 18 | November | Green | |||
| 19 | December | Green |
| Table 2 | |||||
| ID | Name | Rule1 | Rule2 | Rule3 | Rule4 |
| 1 | Monday | Blue | Yellow | ||
| 2 | Tuesday | Blue | Red | Yellow | |
| 3 | Wednesday | Blue | Red | Yellow | |
| 4 | Thursday | Blue | Red | Yellow | |
| 5 | Friday | Blue | Yellow | ||
| 6 | Saturday | Red | Yellow | ||
| 7 | Sunday | Blue | Red | Yellow | |
| 8 | January | Blue | Red | Yellow | |
| 9 | February | Blue | Red | Green | |
| 10 | March | Blue | Red | Green | |
| 11 | April | Blue | Red | Green | |
| 12 | May | Blue | Red | Green | |
| 13 | June | Blue | Red | Yellow | Green |
| 14 | July | Red | Yellow | Green | |
| 15 | August | Red | Yellow | Green | |
| 16 | September | Red | Green | ||
| 17 | October | Green | |||
| 18 | November | Green | |||
| 19 | December | Green |
You are probably better off by using Power Query (PQ) to begin with and skip Text-to-columns. But based on your example, I have used PQ to transform Table1 into your Table2.
Please see the attached file and come back here if you need more help.
Hello Riny,
Thank you. I'm not well versed in Power Query however i was able to see the steps in the query and I have an overall picture of what you did. Thank you. Would you be willing to share each step you took through the 4 or 5 transitions? That would further enhance my PQ knowledge and learning. Thank you.
Glad it worked out and that it got you into learning PQ. It will be a life changer!
With regard to the applied steps, after connecting to the Source I 'unpivoted' the data.
That produces a table with one row for each day/color combination. You may ignore the Attribute column that gets generated automatically as I will remove it later on.
Then, PQ allows you to re-pivot the data (similar to a regular Excel pivot table) but with texts in the 'value area'. In your case, you need one column that contains the column headers and one with the 'color values'. That's why I create a duplicate column.
By the way, you don't need to worry about the code that is created, at this state, as it is all automatic.
Come back here if you get stuck and consider taking a PQ course.
https://www.myonlinetraininghub.com/excel-power-query-course
