Matching people per center of interest

[tiger234]

Hello,

I have two list of people with their center of interest (a, b, c, d, e, f, g...)

I would like to do two things with that :
a/ (random) If I have a list where two name of people are put one next to another, to have their name in green if they have a match (meaning at least one common center of interest) or in red if there is no match.
b/ (all good possbilities) Generate a list of all possible combination of two names (one per list) where people have at least one common interest.

Random (if there is a match, green, if no match, red)

John a,b,c Linda a,b John Linda
Doug c,f Martha e,f Marc Jonna
Marc e,f,g Jonna a, g John Martha
Doug Jonna
Doug Marta


All Good Possibilities
John Linda
John Jonna
Doug Martha
Marc Martha
Marc Martha
Marc Jonna

Ideally I would like to have tis in excel formula and not in macro...

Many thanks for your help,

Tiger.matchtest1.xlsxmatchtest1.xlsxAttachment 302979

[hemesh]

Hello Tiger find attached !

You have mentioned martha 2 times for marc if that is based on their interest then Linda should have been mentioned twice for John.

[tiger234]

Many thanks Hemesh.
Really impressed by the formula... I do not know for how long I would have to study excel to come ump with that...
A question. How do you generate the all possiblities list from H10 to I15? I do not see formula there...
Thanks,
Tiger

[hemesh]

Formula is in C11 then dragged down and to the right

[tiger234]

Thanks Hemesh. This I understood but as you noticed the list of all possibilities I have written from celles H10 to I15 is incomplete.
I was wondering how to go from your table to a table of all possibilities in column such as the example I provide from H10 to I15.

your table is giving John / Linda/ Jonna on one row.
How to make it on two rows in a separate table like?
John / Linda
John / jonna

Thanks,
Tiger

[hemesh]

Find attached

formula used in H11 I have learned from XOR LX and after posting I was thinking about some alternative way so I came up with another simpler solution

copy paste below in H11
=IF(ROW(A1)>MAX($F$11:$F$13),"",INDEX($B$11:$B$13,MIN(IF(ROW(A1)<=$F$11:$F$13,ROW($F$11:$F$13)-10))))
then hold control and shift together and hit enter to make it array formula and then release all three keys. Drag down.

don't delete the helper column because helper column is extracting the data and other formula are based on that.

[tiger234]

Really impressed. Works like a charm. Congratulations!
Thanks,
Tiger!

[hemesh]

You are Welcome !
Thanks for the feedback !

if your query is solved then mark thread as solved.