Excel: counting unique phrases, separated by slashes

Question

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?

Answer 1

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.

Answer 2

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