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 some Excel data that looks a bit like this:

John / Tim      Upheld
Sam             Upheld
Tim / Dave      Upheld
Sam / John      Not Upheld
Tim             Not Upheld
Dave / Tim      Upheld

How can I do the following in Excel, if I can at all?

  1. Count each unique name separated by slashes in column A, and rank them by order of occurences (so Tim[4], Dave[2], Sam[2], John[1]).
  2. Count the number of times each unique name appears with 'Upheld' in column B (so Tim[3], Dave[2], John[1], Sam[1]).

Would this perhaps be better done using a Python script, or similar?

share|improve this question
    
Do you have only two names separated by slashes or could it be any number of names? – Ian Turner Jul 21 '10 at 14:07
    
Could be any number... – AP257 Jul 21 '10 at 14:29

Something could certainly be cobbled together using array formulae, but it would probably be quite messy. I wouldn't want to have a stab without knowing how many rows there are, is this number fixed, are the names known in advance, will the names change over time etc.

I would have thought it better to do it in a script of some description though, VBA would probably be the logical choice.

share|improve this answer

Putting your data in the range A1:B6, and the names is the range C1:C4 the array formula (enter with Ctrl + Shift + Enter rather than just kbd>Enter) in D1 then copied down the column

=SUM(IF(ISNUMBER(FIND(C1,$A$1:$A$6)),1,0))

gives the correct answers to the first part.

The second part then becomes more complicated...

Rather than use array formlulae, you could just use a load of extra columns, two for each name.

So the two columns for John would start:

=IF(ISNUMBER(FIND("John",$A1)),1,0)

and

=IF(AND(ISNUMBER(FIND("John",$A1)),B1="Upheld"),1,0)

Your answers are then just the totals of the columns (which, for neatness could be hidden and the totals used in a nice summary table).

share|improve this answer

You must log in to answer this question.

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