converting pilesorts to numerical data

[mchouse]

This is from a pilesort of items by respondents. The data looks like the following.

Capture.jpg

I have the names of the groups that the respondent placed each item in and would like to convert it to a number. However each respondent has different names, so I'm trying to make it relative to their own categories.

I've been messing around with the following:

=IF(COUNTIF(mm!B$2:B7,mm!B8)>0,(MATCH(mm!B7,mm!B$2:B7,0)),(MAX(B$2:B7)+1))

But cant get it to work.

Any help is appreciated.

Mark

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic