CODEWITHSUNDEEP
google-sheetsformulagoogle-sheets-formula
user11350316
user11350316

Reputation: 29

How to translate QUERY formula in ARRAYFORMULA to automatically populate the QUERY for each row

With my query formula I want to count all duplicate appearances of a certain string in column A (e.g. Apple) and at the same time check if on the same row where the duplicate string appears column B is not empty. Depending if the count is greater than 0 I want to display a message in column C in each row.

In the WHERE clause of the query, I reference the row number of the A column where I want to display the message like so:

…where A = '"&A1&"'…
…where A = '"&A2&"'…
…where A = '"&A3&"'…

…

=IF(IFERROR(QUERY($A:$B;"select COUNT(A) where A = '"&A1&"' and B is not
null LABEL COUNT(A) ''";0)) > 0;"B is not empty for duplicate strings";"B 
is empty for duplicate strings").

The outcome should be like this:

+---+---------+---+--------------------------------------+--+
|   |    A    | B |                  C                   |  |
+---+---------+---+--------------------------------------+--+
| 1 | Apple   | 1 | B is not empty for duplicate strings |  |
| 2 | Apple   |   | B is not empty for duplicate strings |  |
| 3 | Orange  |   | B is empty for duplicate strings     |  |
| 4 | Orange  |   | B is empty for duplicate strings     |  |
+---+---------+---+--------------------------------------+--+

I want to translate this formula into an array formula and put this into the header so the formula will automatically expand.

What I've tried so far:

={"YOUR HEADER";ARRAYFORMULA(IF(IFERROR(QUERY($A:$B;"select COUNT(A) 
where A = '"&A2&"' and B is not null LABEL COUNT(A) ''";0)) > 0;"B is 
not empty for duplicate strings";"B is empty for duplicate strings"))}

This will work only for the second row since my query references column A2. How can I change the formula that it will check each row?

Upvotes: 0

Views: 167

Answers (1)

user11350316
user11350316

Reputation: 29

In another forum, I got the answer. For anyone who's interested:

=ArrayFormula(IF(A2:A="";"";IF(QUERY(ArrayFormula(COUNTIF(QUERY(A2:B;"select A,
count(A) where B is not null group by A label count(A)''");A2:B));"select 
Col1")>0;"B is not empty for duplicate strings";"B is empty for duplicate strings")))

Upvotes: 1

Related Questions