Marty Walker Posted September 5, 2012 Share Posted September 5, 2012 Hi, Got a waaaaaay off topic question to ask the brains here. I've got a csv/spreadsheet that looks something like this: Name | Categories <- Header row Frank | Apple, Oranges, Plums Alex | Plums, Apples, Oranges, Pears Matt | Apples, Oranges, Pears, Lemons ...etc I know how to split the categories column into separate columns. What I don't know how to do is order each category column so that there's an Apples column an Oranges col and so on, so that only the Apples column will contain rows that have an Apple, Orange etc. Cheers Marty Link to comment Share on other sites More sharing options...
diogo Posted September 5, 2012 Share Posted September 5, 2012 Not sure if I get it. Can you put here an example of how you want the table to look like? Link to comment Share on other sites More sharing options...
SiNNuT Posted September 5, 2012 Share Posted September 5, 2012 Where do you want to do this splitting and ordering? In Excel, PW (programmatically), text editor? Do want something like: name | apples | oranges | plums John | √ | X | √ Peter | X | √ | √ or apples (list of names with apples) John oranges Peter plums John Peter Link to comment Share on other sites More sharing options...
Marty Walker Posted September 5, 2012 Author Share Posted September 5, 2012 Hi guys, I have a solution worked out - by manually moving cells around. Here's what it looked liked before: And after: Laborious for sure, but's that the only way I know how to do it. No rush for any pointers as I have it sorted for now. If I can figure out a 'simpler' way to format this for the next time that'd be fantastic. Regards Marty Link to comment Share on other sites More sharing options...
SiNNuT Posted September 5, 2012 Share Posted September 5, 2012 This is not a straight answer to your question but if you work a lot with messy data, cleaning and data transformation you could take a look at Google Refine . This is a really powerfull tool and not hard to use. I haven't used it for a while but i think it would be quite easy to transform the data to the format you desire. 2 Link to comment Share on other sites More sharing options...
ryan Posted September 5, 2012 Share Posted September 5, 2012 I've worked with a spreadsheet exactly like that before and had to do something similar. What I ended up doing was reading it in via PHP, converting the column with the data in it to the format I needed, and then wrote it back out as another CSV. Then loaded back in Excel to work with it. But I'd check out that Google Refine first. If you find that doesn't do the trick, let me know and I can explain how you can do it with PHP. Link to comment Share on other sites More sharing options...
Marty Walker Posted September 5, 2012 Author Share Posted September 5, 2012 Thanks very much diogo, SiNNuT & Ryan. I'll have a look at Refine. It could come in handy for a few other projects of similar ilk. Regards Marty Link to comment Share on other sites More sharing options...
Recommended Posts