1

I've looked all over and found nothing. What I'm doing is showing one table using a sort/filter function in another sheet. I've simplified my formulas for your viewing pleasure, which are as follows:

=SORT(FILTER(A2:J23,
(C2:C23<>L2)+(D2:D23*M2),
(C2:C23<>L3)+(D2:D23*M3),
(C2:C23<>L4)+(D2:D23*M4),
(C2:C23<>L5)+(D2:D23*M5),
(C2:C23<>L6)+(D2:D23*M6),
(C2:C23<>L7)+(D2:D23*M7)),
A29, TRUE,
B29, FALSE,
C29, FALSE,
D29, FALSE,
E29, FALSE,
F29, FALSE,
G29, FALSE,
H29, FALSE,
I29, FALSE,
J29, FALSE)

The goal is simple. I want to simplify them, perhaps by using a range. They're the exact same formulas using one ascending variable.

If it helps, this is what it's doing:

(C2:C23<>L#) checks if col C equals a "type" in col L.

(D2:D23*M#) column M contains check boxes (True/False). Multiply that by arbitrary positive numbers in col D to get array of numbers to OR with first part.

Together, these formulas say, "If type matches, and button is unchecked, don't show row". Boolean logic is A+B' or (A'B)'.

In the sort part, there is a row of check boxes A29:J29 (1/0, 2/0, 3/0, ...). When pressed, the table is sorted by that column, A-Z in col A, and largest first Cols B-J.

EDIT: I've made a mock sheet to better illustrate what's going on, and updated the code to match this sheet. It can be found here: https://docs.google.com/spreadsheets/d/1cOre8sVOb3TE2OsaNC823UB18DAMO4pD4-mZqJtxu0k/edit?usp=sharing

Braxton Istace
  • 45
  • 6

1 Answers1

1

Here is the formula:

=QUERY(
  A2:J23,
    "SELECT * "
  & "WHERE C is not null "
  & IF(COUNTIF(M29:M34, False) = 0, "", "AND NOT C MATCHES '" & JOIN("|", IFNA(FILTER(L29:L34, M29:M34 = False))) & "' ")
  & IF(COUNTIF(A29:J29, ">0") = 0, "", "ORDER BY " & JOIN(", ", IFNA(FILTER(REGEXEXTRACT(ADDRESS(29, COLUMN(A29:J29), 4), "^\D+") & IF((COLUMN(A29:J29) = 1) + (COLUMN(A29:J29) = 3), "", " DESC"), A29:J29)))),
  0
)

enter image description here

This one is a bit different than your previous question, because column letters must be used instead of ColN. More on that here.

And I made 3rd column sorted asc if checked because it is of a string type.

kishkin
  • 5,152
  • 1
  • 26
  • 40
  • Perfect! Thank you for answering both questions! They ended up having the same goal, since I half figured out my problem in the previous question. Sorry you ended up having to figure it out for two different situations, but you did a great job, and they work great! – Braxton Istace Jun 14 '20 at 04:09