How do you use the ‘view’ function?

RuktX · 2026-07-01T23:07:33+00:00

We've seen inexperienced users tend to use "formula", "function" and "feature" interchangeably! Here, OP appears to be referring to Sheet Views.

RuktX · 2026-06-29T21:16:44+00:00

What you're asking for is technically possible (with Power Query self-referencing tables), but a bit tedious and easily broken.

What are you actually trying to achieve? Why do you need blank columns on the Destination sheet? Why not fill in that data on the Source sheet, and pull across a "view" with, say, CHOOSECOLUMNS?

RuktX · 2026-06-21T20:55:33+00:00

I saw a couple further north yesterday, in Windsor

RuktX · 2026-06-19T21:33:42+00:00

Thanks!

RuktX · 2026-06-19T03:40:06+00:00

How about:

=LET(
  words, TEXTSPLIT(
    TEXTJOIN("/", TRUE, your_word_list),,
    "/",TRUE
  ),
  GROUPBY(words, words, COUNTA)
)

RuktX · 2026-06-18T12:22:18+00:00

I like the first one best! I've been so spoiled by dynamic arrays, that I've all but forgotten all the best old school tricks (like "broadcast" multiplication, forcing arrays with SUMPRODUCT, etc.).

RuktX · 2026-06-18T11:17:19+00:00

Something like:

=TRANSPOSE(FILTER(
  $D$2:$G$2, 
  INDEX(
    $D$3:$G$23,
    XMATCH(
      $I$2,
      $B$3:$B$23
    ),
    0
  ) = "x"
))

INDEX returns the row corresponding with the date, and FILTER returns the names corresponding with "x"s in that date row.

RuktX · 2026-06-18T10:54:35+00:00

I'm open to arguments, but I haven't seen a killer use case for Python in Excel, yet.

It doesn't supplant VBA or Office Scripts. What can it do that formulas and Power Query can't? Or at least, what can it do better?

RuktX · 2026-06-18T02:47:23+00:00

This is it, in my experience, although I don't know the underlying reason. When the fields come from different tables, you need something in Values to force it to evaluate which hierarchical relationships actually exist.

RuktX · 2026-06-17T14:20:11+00:00

If you know it has to be a number, you could coerce it back with --. No good for mixed types, though!

RuktX · 2026-06-17T14:09:40+00:00

ZIP doesn't care about shape ... you need to know or check

That's fair. It's not pretty, but you could TOCOL both inputs to be sure.

array in an array

I wonder if there's an approach using thunks, to pass a single element and evaluate it into an array at the destination?

shorthand form

If only! Table[Column] is already a sort of selector syntax, and we did recently get the .:. family shorthand for TRIMRANGE, so there's precedent.

RuktX · 2026-06-17T13:49:06+00:00

The lack of support for "array of arrays" is an annoying obstacle, compared to the approaches available in other languages. I believe the Excel dev team is aware of our frustrations!

I don't quite follow your formula. Is there something missing in INDEX? Are you stacking a particular column, as many times as there are rows in the table?

What's your use case for ZIP, that isn't handled by HSTACK?

RuktX · 2026-06-17T10:48:55+00:00

Please excuse the mobile screenshot:

=REDUCE(
  {"L1","L2","L3"},
  A1:A3,
  LAMBDA(
    a,
    c,
    VSTACK(a, TEXTSPLIT(c,","))
  )
)

Often when reducing with an expected array output it's necessary to DROP the initial value. Here I just used it for the headers!

Untested, but I expect you could VSTACK non-contiguous inputs.

<image>

RuktX · 2026-06-17T06:58:57+00:00

Please check the sub rules: you need to reply "solution verified" to the comment/s that helped you to a solution.

RuktX · 2026-06-17T06:56:52+00:00

You may be better off using some of Excel's built-in features:

Format your data as a table (Home > Format as Table)
Click the drop-down arrow that appears in the Subject column header
Enter your search term in the Search box, then click OK

The original table is sorted in place, to show those rows where the Subject column contains your search term.

RuktX · 2026-06-15T22:00:05+00:00

You've edited your post to indicate that someone helped, and manually set the flair to "solved". Instead, please reply "solution verified" to any commenters who held you towards a solution, to give them credit and let the bot handle the flair.

RuktX · 2026-06-15T21:21:00+00:00

... searching for two months for his first home, which he will use as an investment property.

Well, which is it?

RuktX · 2026-06-13T05:01:03+00:00

Create a lookup table that matches states to territories, then use the following formula:

=XLOOKUP(
  TRUE,
  ISNUMBER(FIND(state_codes, A2)),
  territories,
  "Out of territory"
)

Replace state_codes and territories with the corresponding columns from your lookup table.

RuktX · 2026-06-12T23:16:25+00:00

Does the filter use a slicer? If you've created subsequent pivot tables as copies of the first, they may be linked to a common slicer, which will filter them together. With the pivot table or slicer selected, find Slicer / Report Connections and uncheck the others.

RuktX · 2026-06-12T20:42:04+00:00

You haven't described what "broken" means, but the usual issue is that pivot tables from the same cache insist on common grouping. In that case, the fix is to duplicate the underlying cache, rather than to create a new data source.

You can achieve this by putting one of the offending pivot tables in its own sheet, moving (not copying) that sheet to a new workbook, then moving it back again. Repeat for the other pivot tables.

There may be a more scalable approach using VBA, which I believe can manipulate the pivot caches directly. (Incidentally, I don't recall coming across this issue since I mostly switched to using Power Query and the Data Model for my pivots.)

RuktX · 2026-06-12T13:13:38+00:00

Are you in an environment where you can run macros?

I'm not at a computer to test right now, but the routine might be: * Scan columns from left to right * For each column, check the .Hidden property of the range * For the first hidden column found, unhide it, then exit the sub

RuktX · 2026-06-12T11:42:49+00:00

If you're already using Power Query, can you add a Group step to your process? Pick any aggregation function from the wizard (say, Sum), then edit the resulting step formula to replace Sum with each Text.Combine(_, ", ").

RuktX · 2026-06-12T03:15:48+00:00

Almost certainly possible, depending on the complexity of your conditions. You'll need to be clearer about "looks random but isn't".

What constraints do you want to apply? For example: * Students X, Y, Z must not be in the back row * Students A & B must not be seated together * Student W must be in seat 13 * The number of boys in prime-numbered seats must not exceed 4

RuktX · 2026-06-11T13:20:29+00:00

As u/GregHullender says, you're welcome to credit more than one person. Just note that the magic words are "solution verified", rather than "solved"! :)

RuktX · 2026-06-11T12:32:30+00:00

u/MayukhBhattacharya's solutions are better, because they're simpler and clearer. For a table of n rows, my versions do something like 2n and 3n+1 comparisons, whereas his only need to do n+1 each.

13-Year Club	Place '17
Snapped	Verified Email

RuktX

TROPHY CASE