Sorting/grouping when there are multiple values in one cell

Question

I have an Excel 2007 spreadsheet, where each row of the dataset describes a feature of a piece of software.

One of the columns in the spreadsheet is Relevant Users, which describes which users of the software the feature is of interest to. There may be a couple of different users interested in a feature, in which case I've been filling in the cell with the two user types separated by a colon, e.g. 'Usertype A; Usertype D'.

Occassionally, I'd like to sort my data by the Relevant Users column. However, the way I'm populating the column means the sorting isn't very smart. If I have a feature where 'Relevant Users' is 'Usertype A; Usertype D', and then I sort by Relevant Users, that feature will be grouped at the end of all the other features of relevant to Usertype A, as it's just sorting alphabetically. But I want it to be listed in the two separate groups of Usertype A and Usertype D.

Or, if I have a pivot table that groups the features together under the heading of Relevant User, I'll get all the features for 'Usertype A', then 'Usertype B', then 'Usertype C', then 'Usertype D', then 'Usertype A; Usertype D', etc. Whereas I really want a feature with Relevant Users as 'Usertype A; Usertype D' to show up in both the Usertype A group and the Usertype D group.

I guess if this information was in a database I might have a many-to-many table linking Relevant Users to features. But is there a way to go about having this kind of many-to-many relationship in Excel?

Answer 1

The easiest way would probably be to put all the values in separate columns, e.g. make a column for each usertype, put like a "yes" in valid columns, and sort them individually. It's maybe less elegant that what you're looking for, but it's simple.

Answer 2

I think the most scalable method is to use a different row for each feature-role combination. It's also ideal for pivoting.