help on find function

Hello everyone.
I am stuck finding a formula/function to achieve a specific result.
Let me explain.

I have 2 sets of 3 columns. ABC and DEF

In columns ABC I have a list of 3-cell sets (name,city,age) for example:


MARY-NYC-40
LUKE-LAS-25
BOB-PAR-25
SUE-NYC-28


In columns DEF same thing
PAUL-LON-15
SUE-PAR-28
CARL-LAS-25
MARY-NYC-28


Now, what I need is that for each set of A, the formula/function tells me
1) IF there is any set in column B where 2 out of 3 cells are the same and 2) pulls out the one that is not there.


With the examples above, it should be:

MARY-NYC-40 YES & 28
LUKE-LAS-25 YES & LUKE
BOB-PAR-25 NO
SUE-NYC-28 YES & SUE & 40

last one, there are 2 matching sets in column B to meet criteria that 2 out of 3 cells are valid, therefore result should show both.

I hope that is clear, I don't know if a macro can do that but I don't think it's needed; but maybe I am wrong.

The results are one per cell. for the first set above in a cell it should write YES in another should write 28.


A big big thanks in advance to anyone that can help me with this

Thank you Jason
Your suggestion is good and I found another place in my workbook where to use that.
I am now stuck again with another point.

Let's still think at columns ABC.

MARY-NYC-40
LUKE-LAS-25
BOB-PAR-25
SUE-NYC-28
SAM-LON-42
JOE-LON-30
BOB-LON-12

And consider that I have a macro that every time I run it, adds one more line with a combination.


My goal is to find the quickest way to identify the first 3 sets that have nothing in common with the others. From the example above, it should give me:

MARY-NYC-40
LUKE-LAS-25
SAM-LON-42

Now, to do this I have already a combination of formulas that work perfectly, where the principle is to exclude from the list every line that has something in common with any of the above... meaning that basically line 1 is always the first of my 3 sets.

But look at another example and follow me:

1: MARY-NYC-40 - this is picked as 1st Set
2: LUKE-LAS-25 - this is picked as 2nd Set, since there is no value in common with above
3: BOB-PAR-40 - this is ignored, because 40 is in common with 1st Set
4: SUE-NYC-28 - this is ignored, because NYC is in common with 1st Set
5: SAM-LON-42 - this is picked as 3rd Set

Meaning that I need to wait 5 macro runs to get my 3 sets.

However, lines 2 3 and 4 are actually 3 sets with no values in common. Meaning I can have my three sets after just 4 macro runs.

But how!?


Are you (or anyone else) able to help?

With a formula rather than macro, list starting in A2, enter this formula in D2, then fill down.

=IF(A2="","",IF(COUNTIF(A$1:A1,A2)+COUNTIF(B$1:B1,B2)+COUNTIF(C$1:C1,C2),"Ignored",CHOOSE(COUNTIF(D$1:D1,"<>Ignored"),"1st","2nd","3rd")&" Set"))

The key part is the bit in red, which counts the number of values in any given row that match any of the above, those with nothing in common with any of the above rows will have a zero result. The rest of the formula is just there to turn those results into something useful.

Hi Jason,
I tried, but the formula returns the result I was already achieving. With the first set to be the first line.

MARY NYC 40 1st Set
LUKE LAS 25 2nd Set
BOB PAR 40 Ignored
SUE NYC 28 Ignored
SAM LON 42 3rd Set


whilst what I need is
MARY NYC 40 Ignored
LUKE LAS 25 1st Set
BOB PAR 40 2nd Set
SUE NYC 28 3rd Set
SAM LON 42 Ignored as found already 3 sets.


thank you for your help

That not fully true I am afraid. The ultimate goal is to find the sets with the lowest number of macro runs, and each macro run adds another line.
With your formula I need to wait 5 runs, but actually already after 4, if the first was ignored, I have my 3 sets.

I don't think it's true to say to ignore the first row.
Consider the very first example of values I put..

MARY-NYC-40
LUKE-LAS-25
BOB-PAR-25
SUE-NYC-28
SAM-LON-42
JOE-LON-30
BOB-LON-12

in this case it would be correct what your formula does, so sets will be rows 1,2 and 4.

But with the other example this won't be the case

In order to do what you want we need to understand the logic and reason behind the results you expect.

From the example

MARY NYC 40 Ignored
LUKE LAS 25 1st Set
BOB PAR 40 2nd Set
SUE NYC 28 3rd Set
SAM LON 42 Ignored

I don't understand why
The first row is ignored, when it was the 1st set in another example, why is this one different?
Why the 3rd row (2nd set) is not ignored because 40 matches the first row.
Why the 4th row (3rd set) is not ignored because NYC matches the first row.

I need to find 3 sets of values in the lowest number of macro runs.

I do not mind the results, there is no "correct" set or wrong. The sets are valid as long as they are 3 and each of them has values none of the others have.

To answer your questions:

The first row is ignored, when it was the 1st set in another example, why is this one different?
it is not different, but if this was ignored, the next three would qualify as valid sets, otherwise, if I keep this as set 1, i need to have a fifth row in order to have 3 sets
Why the 3rd row (2nd set) is not ignored because 40 matches the first row.
Why the 4th row (3rd set) is not ignored because NYC matches the first row.
because the first row, in this case, is being ignored


I am now testing a sheet where I put from column E a =A fields etc with a formula to blank if in the other group of columns this was marked as valid set. This way it could work.

Now I follow

I'll give it some thought and see what I can come up with, might not get back with anything until tomorrow though.

thank you Jason but just FYI I think that doing your formula on several columns, removing one by one the top row is working. testing now

I've still had no luck in figuring out a formula for this. If this was my own file I would run the checks as part of the macro by running back up the list each time a new name was added.

Would that work for you?

Hi Jason,
I found a way actually.
The first set of formulas works as initially setup: checks the first valid set, then excludes any partially matching until it finds 3 different sets.
The second set of formulas checks the first valid set, then the same formulas are copied underneath with a reference above to start again, excluding set 1
A third set of formulas then does the same, but in this case ignores the second set found.
Another fourth bunch of formulas checks all rows but ignores those that were containing a value already in any of the above rows.

This way, with all 4 sets of formulas grants to cover all scenarios (Or at least this is what I think LOL), then a vlookup and countif formulas to identify the first bunch where a set 3 is found.


Example
The first group of formulas would find these sets

ABC
DEF
HRB
BUP


The second group finds below in bold because I tell it to ignore the first set found (ABC)
ABC
ARZ
NBD
URC

bunch 1 would have ignored all the 3 in bold since each of them has a value in common with ABC



The third finds this, ignoring the 2nd found (DEF)
ABC
DEF
DOP
REF

The first bunch would have ignored ABC, but DOP and REF have a value in common with DEF, so no sets found...

The second bunch would have ignored ABC, set as 1 DEF but the two underneath clash


The fourth finds the 3 sets in this case
ABC - 1
COF - ignored as C in ABC
FUR - 2
BAL - ignored as B&A in ABC
LON - 3


First group would have ignored all underneath ABC, because there is always a value in common with any of the previous lines. So only 1 set found
Second group would have ignored ABC, set COF as 1, excluded FUR with the F in common, BAL as 2. LON has L in common with BAL. So no 3rd set
Third group would have had: ABC as 1, COF clashing with ABC, FUR is ignored being the actual 2, BAL clashes with ABC and LON clashes with BAL.


I hope the above makes sense. Consider that when I say "ignore" i basically put a blank instead of the value so the systems does not find it.

I think that with the 4 logics above all scenarios are considered.


Happy days