I've got a service (phplist, a newsletter manager) that exports a user list with several fields. At the end of it, each user has one or more lists to which he has subscribed.
The problem is, that table is not ordered as I'd like to, and instead of creating a new column for each lists, each rows creates the columns it needs. This is an example:
What I would want is, if I've got for example eight newsletters (lists), to be able in Excel to convert that table to one that creates the proper columns and fills the data within. The result of the previous table converted would be this:
Or something similar (instead of a Yes or blank, I could have a Yes and No, whatever). This way I could filter the table by list, which is impossible with my current table: the columns in the source table, as you can see, can contain different lists on each row. Is this possible in Excel?
Thanks to W_Whalley I was able to find the real answer to the problem. If somebody has used PHPList, this newsletter manager allows you to download the list of subscribed users, but as I mentioned on the original question, it doesn't give you the lists they are subscribed to in a good way. In fact, it gives you a final colum with all the lists in the same cell. That is slightly different that the problem I considered, because a row of that table would be:
Name | Surname | Email | Lists John | Perry | email@example.com | List1 List3 List6 List 7
Name | Surname | Email | Lists John | Perry | firstname.lastname@example.org | List1 | List3 | List6 | List 7
I proposed the second table because I thought it was easier to manage, but it wasn't. In fact, I had to make a little modification to get different colums for each list after exporting the user list from PHPList. This was not neccessary.
I exported the user list right away, and the solution was to apply the formula W_Whalley suggested considering only one column at a time. Doing this for several columns worked. The final formula (using an example row and column) was:
Or, in spanish version of Excel (the one I was using) with an example column:
Hope this is useful for somebody out there. Thanks everyone, specially W_Whalley!!