Counting lists

[gtg836b]

Hello, Thank you in advance for your help.

Data:
I have a list of names entered in a worksheet as follows: In Column A is the first group. For rows 1-7 I have a name. Each column after that contains 7 more names. They correspond to players on a team that were on the field for each point in the game. I want to be able to count the number of times the same players were on the field.

For example ( groups of 3 vertically)

Amy Brad Devin
Brad Amy Mike
Devin Devin Brad

I need excel to give me results that the combination of Amy, Brad, and Devin - in any order - occurred twice and that the combination of Devin, Mike, and Brad occurred once.

I am familiar with the IF & COUNTIF functions.

Thank you.
.

[DonkeyOte]

Please post a sample file with a meaningful amount of sample data and provide also some expected results for the same.

As it stands it's not entirely clear if you're looking for instances wherein all 7 names repeat or only subsets thereof.

[gtg836b]

I have attached a worksheet as requested. Thank you.

[DonkeyOte]

See attached as proof of concept

There are two approaches shown - both utilise Chip Pearson's QSortInPlace UDF which allows you to create an ordered string of the various names involved in each point.
(note this means you must enable macros when opening the file else #NAME? errors will result)

Doing the same (ordering names) without a UDF is viable but if you can use the UDF it I would advise it

The second approach of the two I mention shows how to use the ordered strings to first return all of the unique name combinations that appear and then subsequently the frequency of that combination across all points.

[gtg836b]

Thank you. Very helpful! Essentially I need to order the columns alphabetically, create a string and count the string's occurrences.

FYI, I've decided to implement the ordernames UDF to create the sorted array for each lineup and then countif for frequency.

[DonkeyOte]

I'm glad it helped.

To reiterate the UDF is driven by the QSortInPlace code (this does the hard work) and is something I use quite frequently for this type of exercise.

You can find more info. on this routine from the authors website: http://www.cpearson.com/Excel/SortingArrays.aspx
(see also: http://www.cpearson.com/Excel/VBAArrays.htm)

The same can be achieved without need for VBA but the UDF makes things a lot cleaner from the worksheet point of view.