Tricky: How to check if two different values are in the same column

Good evening,

Having the following data:

Col 1	Col 2
A	B
B	C
C	D

Determine if two given values exist in the same column. In this case, should only be false for the combinations of A and D (order doesn't matter)

I know something like this would work:

OR(

AND( ISNUMBER(MATCH("A",Col1,0)) , ISNUMBER(MATCH("B",Col1,0)) ),
AND( ISNUMBER(MATCH("A",Col2,0)) , ISNUMBER(MATCH("B",Col2,0)) ),
...

)

I'm hoping there's a better way of doing this - If not, every time I add another column, I'd have to review this function - bad idea


Tried thinking about the issue in a different way, but couldn't come up with a solution either.
In this case, return true if two given values are shared with the same key:

key	value
Col 1	A
Col 1	B
Col 1	C
Col 2	B
Col 2	C
Col 2	D

Thank you for your time

Are you opposed to using conditional formatting to find duplicates or are you truly just wanting a formula for TRUE, FALSE?

Originally Posted by FlameRetired

I also think this will work in C2, but try it on a few samples to see.

Formula:

Please Login or Register  to view this content.

These are all assuming the values in column B are representative of the real data.

Thank you for the quick reply, and holy moly that looks like a tiny thing! Will check if it solves my issue

ok, maybe I've expressed it wrong. Here's the thing:

everything inside the table is actual data, volatile data: everything may change at some point.

I have to group things into categories, let's say this is my data:

foo	item
foo	data
foo	stuff
foo	this
bar	this
bar	that
bar	item
other	something
other	else

And this is my input and output:

item + data = TRUE (both share "foo")
stuff + that = FALSE (one is in foo, the other is in bar)
something + else = TRUE (both share "other")

all data can be changed at any point. It's the actual check that has to keep working regardless

Hey, a solution has just been presented to me via discord from a friend of Mine, which works perfectly for what I'm after.

MAX(

(

FILTER(A:A,B:B=H3)
=
TRANSPOSE(FILTER(A:A,B:B=H4))

)*1

)=1

Where A:A is the first column, and B:B the second
H3 is the first keyword to check, and H4 is the second

Great usage of arrays right there


Thank you all for your time.

Problem is you'd need either a 3-D array or a way to iterate through columns or through "A", "B" and perhaps more values. Neither are available using only built-in worksheet functions.

If your columns would always be in a single, multiple-column range with each column having the same number of rows OR any column with fewer rows having blank cells where the other columns have data, name the range containing all the columns Cols. Then if there were only 2 values, "A" and "B", try

Formula:

Please Login or Register  to view this content.

Since you show you're using Office 365, you should be able to enter this as a regular formula. This would need to be entered as an array formula in previous versions, so [Ctrl]+[Shift]+[Enter].

The --(Cols="X") terms are arrays of 1s and 0s depending on whether values in Cols equal "X". The MMULT(...) calls reduce that to horizontal arrays of the number of instances in each column of Cols, so given your data, {1,0} for "A" and {1,1} for "B". The SIGN(...) calls reduce those to 1s for positives and 0s for 0. Adding the results of the SIGN(...) calls gives 2 when both "A" and "B" appear in a column, 1 if only one of them appears, 0 if neither appears. So the OR(...) call returns TRUE if one column in Cols has both "A" and "B", FALSE otherwise.

This is relatively inefficient. VBA might be better for something like this.

Please Login or Register  to view this content.

If the range of columns were named Cols, call this user-defined function in formulas like

Formula:

Please Login or Register  to view this content.

I figure for a large number of values ({"A";"B";...}) and large Cols range, this may be faster to calculate than complex formulas calling several built-in functions. The optional arguments make this broadly applicable, though you could extend Anum and Bnum semantics to negative numbers meaning all less Xnum, so if the B argument had, say, 5 columns, Bnum of -2 would mean find Anum matches in 5-2=3 columns in B. Left for you or others.

Originally Posted by hrlngrv

Problem is you'd need either a 3-D array or a way to iterate through columns or through "A", "B" and perhaps more values. Neither are available using only built-in worksheet functions.
(...)

I'm sorry you went through all that :/
I've posted the solution given to me, which is already working. Didn't have to go with VBA

It's basically creating a 2d array of booleans, where each cell is true only when both col (FILTER A) and row (TRANSPOSE of FILTER B) match the same value.

If A1:A3 contained {"A";"B";"C"}, B1:B3 contained {"B";"C";"D"}, A4:B1048576 were all blank, H3 evaluated to "A", and H4 evaluated to "B" (close to the example in your original post above), FILTER(A:A,B:B=H3) would return a #CALC! error since "A" wouldn't appear in B:B, so FILTER would have nothing to return. [Tangent: wouldn't #NULL! be more appropriate for this particular situation? Consistency isn't a MSFT strength.]

As shown, I don't see how the formula you show produces anything other than an error using data from your original post.

You had mentioned wanting to avoid editing formulas if you add more columns. How could you add columns without needing to edit the formula in your post immediately above since the formula only includes A:A and B:B?