Finding the number of common entries for each entry in a table

[tek]

For every single unique entry in a column A of a table, I want to find the number of times it has a common entry in column B with each other unique value in column A! Sheet attached with an example. In the real data there might be as many as 10,000 rows.

For the first name in column A, john, I need to look at the value in B, 123, and see that steve also has an entry with 123 in column B.

For david and chris there are 3 values in column B which are common to both.

Hope this makes sense! I can change the layout of any column or table, pivot tables, standard formulae and macros are all fine (by fine I mean I am happy to use them, not necessarily that I know how!). The values in B can be alphanumeric, have spaces, and be any format really, just looking for unique values. Upper and lower case can be ignored. Column A will be all names, no digits. In the example it is sorted on B, but can move it around however.

So far I have been thinking about MATCH to go through each unique value of B taken from a separate pivot table, but can't make it work.


Any suggestions much appreciated,

thanks,
Tom

[Ron Coderre]

Using your posted workbook.....
(see attached)

Enter this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of ENTER) into cell F2:

Please Login or Register  to view this content.

Copy F2 and paste into F3:F8
Then....Copy F2:F8 and paste into G2:L8

Is that something you can work with?

[t_e_k]

Ron,

Superb! Thank you. Yes that is very workable. So the first IF just makes sure it only gives a value for the upper right triangle? I think I even almost understand it. Does the 1/MATCH give a DIV/0 error when the IF is 0, so that the ISNUMBER returns a FALSE/0?

I have changed the arrays to named arrays. If I was using a table for the data set, would this function be happy with table references? Anyway, I am going to give that a go. Thanks so much for the help,

Tom

[tek]

I seem to have accumulated two different usernames! No wonder tek was taken when I registered again recently!