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 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

2 Answers 2

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

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.