Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
How many different usertypes are there? – Sux2Lose Apr 15 '10 at 16:36
There's 10 so far, and it could increase -- I guess 'role' would be a better word to use than 'usertype'. – ngm Apr 16 '10 at 8:18
up vote 2 down vote accepted

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.

share|improve this answer
Thanks, I'll give that a go, but with 10 or more usertypes, as you say it probably won't be elegant, so I may look for another solution. And I'm not sure how it would work with pivot tables. – ngm Apr 16 '10 at 8:22

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .