CODEWITHSUNDEEP
excel
Hadron Tungsten
Hadron Tungsten

Reputation: 157

Using Excel to find whether two members of a group appear in another group

I have a sheet of some 2000 rows that runs like this:

GROUP       PERSON
A Group     John Croft
A Group     Mike Federer
A Group     Billy Placente  [ed: NB]
A Group     Louise Sheen    [ed: NB]

B Group     Mitch Balm
B Group     Meghan Gore
B Group     Elvis Kai

C Group     Eric Almont
C Group     Billy Placente  [ed: NB]
C Group     Louise Sheen    [ed: NB]

I'm looking for a formula that will be able to tell me when two or more people from one group appear together within another group (e.g. the highlighted rows above).

Ideally the output would be a third TRUE/FALSE column indicating whether the person in that group/row appears in another group with another member of that first group.

I'm thinking that this might be doable using array formulae but beyond that I've no clue.

Thanks!

Upvotes: 1

Views: 416

Answers (1)

GalAbra
GalAbra

Reputation: 5138

I'm afraid the best way would be a bit of a work-around, by using a connecting column.
Each cell in the column will contain data about a 2x2 "square" of cells A2, A3, B2, B3 (and the consecutive row numbers, of course).
Then we'll be able to compare every "square" with the other squares.

A possible implementation would be:

      A        B          C             D
1   Group    Person
2   A        Mike      Mike-Joe       TRUE
3   A        Joe       Joe-Tim        FALSE
4   A        Tim                      FALSE
5   B        Morty     Morty-Rick     FALSE
6   B        Rick                     FALSE
7   C        George    George-Mike    FALSE
8   C        Mike      Mike-Joe       TRUE
9   C        Joe                      FALSE

Where the formula in C2 (and similarly in C3, C4, etc.) would check if the next line contains a person from the same group (otherwise it's left empty):

=IF(A2=A3, B2&"-"&B3, "")

And the formula in D2 (and similarly in D3, D4, etc.) would be:

=AND(C2<>"",  COUNTIFS(C:C, C2, A:A, "<>"&A2) > 0)

Which checks if the corresponding cell in column C isn't empty and if there's another cell that equals to the corresponding cell in column C, but differs in column A.

Upvotes: 2

Related Questions