Create intermediate Works with: Any AI

Dynamic Array Formulas: FILTER, SORT, UNIQUE

Structured brief Has example

Replace manual filtering with live formulas.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Help me use dynamic array formulas. App and version: [SPREADSHEET_APP_AND_VERSION]. Data range and columns: [DATA_RANGE]. What I want (a live list of rows that meet conditions, sorted, without duplicates): [DESIRED_OUTPUT]. Sample data (fake): [SAMPLE_DATA].

Give me, with explanations:
1. FILTER: =FILTER(A2:D100, (C2:C100="Open")*(D2:D100>1000), "No results") for multiple conditions with AND logic; OR logic using +; blank handling.
2. SORT and SORTBY: sort the filtered result by one or more columns.
3. UNIQUE: list distinct values and count each with COUNTIF alongside.
4. Combining: =SORT(UNIQUE(FILTER(...)), 1, 1), and how to wrap with LET for readability if my version supports it.
5. Spill behavior: what the spill range is, what #SPILL! errors mean (blocked cells, merged cells) and how to fix them.
6. Referencing the spilled result with the # operator or a defined range.
7. Fallbacks for older versions (helper columns with INDEX and SMALL, or a pivot table).
8. Performance and limits on large data.

For my data, write the exact formulas and show the expected output as a small table. Add three test cases (no matches, one match, many matches) and a checklist for making the sheet robust when rows are added.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Open deals above 1,000, sorted by amount:
=SORT(FILTER(A2:D100, (C2:C100="Open")*(D2:D100>1000), "No results"), 4, -1)
Explanation: the product acts as AND; the third argument handles no matches; SORT uses column 4 descending.
Distinct customers: =UNIQUE(A2:A100). Combined: =SORT(UNIQUE(FILTER(A2:A100, C2:C100="Open"))).
Expected output for the sample: 3 rows (Acme 4,200; Birch 2,750; Cobalt 1,300).
#SPILL! means cells in the way; clear or move the formula. Older versions: helper column plus INDEX/SMALL. Make it robust: leave free space below the formula, avoid merged cells in the output area, and test with no matches (shows "No results"), one match, and many matches before sharing the sheet.

• dynamic-arrays
• structured-brief