0

How would I create this formula to be applied to Column C (returning a boolean)?

If B2 contains "apple", see if the number in A2 exists in any other A cells whose row doesn't contain "apple" in the B column. (Assuming other rows also have "apple" in the B column and may or may not have a duplicate number in the A column).

Here is some example data and the desired result in Column C: enter image description here

2
  • please edit the post to include some mocked up data in a tabular format and expected output. It will help us understand your need. Dec 7, 2021 at 21:14
  • @ScottCraner done
    – gills
    Dec 7, 2021 at 21:46

2 Answers 2

0

Use IF and COUNTIFS:

=IF(B2="apple",COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"")

First we check if B2 is "apple"

Second we check if any that are apple then we see if there are any that match A2 that do not contain "apple" in column B

enter image description here

0

Quick answer:

You can do this using array formulas. In your cell C2, write the following:

=IF(B2="apple",SUM(ISNUMBER(A:A)*(A:A=A2)*(B:B<>B2))>0,"")

then use Ctrl+Shift+Enter to register it as an array formula. Copy it to the rest of the column afterward.

Explanation:

The IF function is the ordinary sort: if in the B column we see “apple”, we do stuff, otherwise just write the empty string. The interesting part is the second argument — that which gives us the result in the “apple” case.

Now first consider what Excel would do with a simpler formula: SUM(A:A=A2). Inside the SUM, Excel sees an equality of the form “range = value”. If both sides were simple values, that would evaluate to a boolean, but here because you used Ctrl+Shift+Enter to turn on array formulas, Excel applies the operation to each element on the left side individually, and stores them in memory in a temporary array, which SUM happily accepts. The result is thus the number of cells in column A that are equal to A2...

Well, almost. This would be the case if SUM treated TRUE values as 1 and FALSE values as 0. That not being the case, we would need to convert the booleans into numbers, one way being a double negation like so: SUM(--(A:A=A2)). The * operator in the actual formula will take care of that too though.

Back to the actual sum, we have the additional term ISNUMBER(A:A). The principle is similar: this will again operate elementwise on column A as it doesn't ordinarily know what to do with a range. Then the * operator “multiplies” the two temporary ranges containing booleans elementwise — basically applying the AND operation to them — and giving us a new range of booleans. (This is here just to ensure that blank cells are not counted as equal to 0, and NOT(ISBLANK(A:A)) would likewise work.)

Finally we do the same with the additional range of booleans that describe whether each individual value in column B is not equal to B2. In the end, SUM then acts on the resulting range.

In conclusion, we counted rows with numbers in the first column, where the current row is match in column A, but not in column B. Your condition just says that the number of these is positive.

Remark 1:
We use the * operator instead of the AND function because the latter happens to accept ranges as argument by default, so it would just eat up the temporary ranges instead of activating the “array mode” and performing an elementwise operation.

Remark 2:
You can freely make the columns absolute for copying the formula to other columns, add (absolute) row numbers to the ranges A:A and B:B to ignore other stuff potentially located in the same column (as well as performance) — or even make named ranges of the two columns like IDs and Fruits, then put these names in the formula instead of the ranges.

3
  • Thanks for such a thorough answer! I want to mark this one as the solution, but in the end, I couldn't understand it even with the thoughtful explanation (I begin to get lost in the explanation of what SUM(A:A=A2) would do, e.g. regarding "both sides", booleans vs numbers, and adding total number of matching cells). Someone else was able to provide a simpler formula that yielded the same results (which I think was more processor efficient after trying both), so I'm going to mark that one, but I'm glad this answer is here for more intelligent minds to learn from than mine!
    – gills
    Dec 8, 2021 at 0:29
  • @etudes Yeah, in practice — as noted in Remark 2 — you should definitely replace A:A and B:B with something like A$1:A$100 and B$1:B$100, because unlike with specialized functions, in a generic array formula Excel has no guarantee you won't need the blank cells, hence it processes the entire column of more than a million cells (in our case entirely futilely). For your use case, there in fact existed a relevant specialized function as pointed out in the other answer, but in general array formulas are an extremely powerful tool, and it is very useful to familiarize yourself with them.
    – Taederias
    Dec 8, 2021 at 2:33
  • ahh got it, ok. will make it a point to learn more about array formulas in that case, thank you!
    – gills
    Dec 8, 2021 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.