Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel sheet that has a three fields:

USER_ID | DATE_CREATED | TAGS

Tags applied to the items in the sheet vary quite a bit, and are set up like |tag1 tag2 tag3...| within the field.

I want to get a list of all unique user IDs that acquired a certain tag at any point (tagx), but did not have another, different tag at any point prior to that (tagy).

Given the variability in times when the users could have acquired tagx, I'm not sure the best way to set this up.

How would I go about structuring my worksheet to filter for this information? I'm happy to set up additional helper columns that do testing, and to create pivottables.

EDIT: updated to better explain -- the fields included are just those fields that are available to me to complete this sort. The output that I'm looking for is, in any form, a list of all unique user IDs that acquired tagx at any point, but did not have tagy before they acquired tagx.

EDIT(1): here's a picture demonstrating what I'm looking for:

example data

in this case I would want a way to grab ID #2 (and others like it) because it acquires and retains tagx on 1/3/2001 and does not have tagy before it

but I would want to exclude ID #1 because, although it also picks up and keeps tagx on 1/2/2001, it has tagy beforehand

share|improve this question
1  
Can you give an example how it should look prior and after the filtering? Maybe with 2 little sceenshots –  nixda Jul 31 '13 at 22:02
    
I don’t understand how the “date created” really fits into the question. Please explain that more clearly, and/or illustrate it in your example. It would probably help to have examples that cover all possible combinations that you want to distinguish among: e.g., a user that had tagx but had tagy earlier, a user that had tagx but got tagy later, a user that had tagx but never had tagy, a user that never had tagx, etc. It would probably also help it you described your real-world problem. –  Scott Jul 31 '13 at 22:40
    
now updated with better explanation –  fox Jul 31 '13 at 23:28
    
So you want to go through the ID column and find the first time (chronologically) that each ID gets tag (any) and then check to see if that is tag (x) and if not, go to the next unique ID? And then what? Highlight each one that fits criteria? –  Raystafarian Aug 1 '13 at 23:31
    
close, the tags field will actually have an arbitrary number of tags in it separated by a space, I want to find the first instance of tagx specifically and make sure that no prior rows have tagy in them. –  fox Aug 1 '13 at 23:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.