google sheets array formula for boolean logic based on duplicate numbers per cell

Question

I have a column A that looks like:

11

11 11
5 6
5 5
5 6 6 5
11 11 11 11

6 7 8
3
3 2 3

and seeking for an alternative-smarter way how to output TRUE/FALSE as shown here:

	desired result
11	TRUE
	FALSE
11 11	TRUE
5 6	FALSE
5 5	TRUE
5 6 6 5	TRUE
11 11 11 11	TRUE
	FALSE
6 7 8	FALSE
3	TRUE
3 2 3	TRUE

The logic is:

FALSE if
- cell is empty
- there are no duplicate numbers per cell
TRUE if
- cell has only one number
- cell has two or more duplicate numbers regardless of the order

More notes:

numbers are separated by space
numbers can varry in range from 1 up to 80
each cell can hold maximum of 40 numbers separated by space
each cell can hold minimum of 1 number or be empty

How can I solve it:

I can sum it (in various ways - LAMBDA, QUERY(SUBSTITUTE(, SUM(SPLIT(, ...) and devide it by count. if result is whole number its TRUE, if result has decimal places its FALSE but this wont work for 3 2 3, 6 7 8.

Another way would be to split it and use QUERY and count numbers per row, group by row_numbers and pivot by all_numbers, via LET then wrap it into IF and remove numbers lower than 2. Next smash it with TRIM(TOCOL(QUERY(TRANSPOSE( and LEN it. Then IF it like >0 is TRUE and add rest of the logic for cases not covered by the QUERY.

Formula would be enormous in either way, so I'm looking for some "smarter/shorter" way (if any). Maybe there is some magic regex patern I am not familiar with?

z.. · Accepted Answer · 2025-04-22 23:16:36Z

5

Assuming the data is in A2:A, You can use the following formula in B2 and drag it down.

=ARRAYFORMULA(LET(s,IFERROR(SPLIT(A2," ")),OR(COUNTA(s)=1,COUNTIF(s,s)>1)))

Or with a single formula in B2:

=ARRAYFORMULA(
   BYROW(
     IFERROR(SPLIT(A2:INDEX(A:A,MATCH(,0/LEN(A:A)))," ")),
     LAMBDA(s,OR(COUNTA(s)=1,COUNTIF(s,s)>1))
   )
 )

How it works

First, we split the string by the space and assign the result to s

s ← IFERROR(SPLIT(A2," "))

Then we use the OR function to return TRUE if the splitted string has only one value or has at least one value that occurs more than once

OR(COUNTA(s)=1,COUNTIF(s,s)>1)

edited Apr 22 at 23:16

answered Apr 22 at 23:09

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

google sheets array formula for boolean logic based on duplicate numbers per cell

1 Answer 1

How it works

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

How it works

Comments

Your Answer

Sign up or log in

Post as a guest

Related