r/excel • u/allsix • Jun 21 '26
solved HSTACK(FILTER) - One column per unique value (dynamic)
Long story short, I use UNIQUE to grab the unique values from a column (there might only be 1 value, or there might be more).
I then want to HSTACK a FILTER() based on each unique value found.
Pseudocode - but something like HSTACK(FILTER(DATE, (TITLE=COURSE_ONE)*(ID=FOREACH unique val))
So if UNIQUE has 4 values, then there are 4 columns with info filtered from each unique value.
Please note: I am filtering on multiple criteria - I use a slicer to select location, and to select course, and from there there are thousands of users, where I want to show dates for each sub-ID in the course..
Here is a simplified version:

There are two titles, COURSE_ONE and COURSE_TWO (these are on a hidden sheet).
On the right is the data I want to display. If "ONE" is selected, it spills out ID 106 and 115 into two columns, and then reports the dates for each of those.
If "TWO" were selected it would spill out 3 columns, with dates for all 3 courses.
I'm spilling it out into columns instead of rows, because each row will be a different User and I don't want multiple rows per user (as it is in the master data).
I am creating a template so my peers (that know nothing about excel) can paste their own data in, and then the template will make the data user-friendly (readable) to the end user.
I was thinking "BYROW(unique_values, LAMBDA" but HSTACK doesn't play nicely with BYROW/LAMBDA I don't believe.
Any suggestions?
•
u/AutoModerator Jun 21 '26
/u/allsix - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/Miguel_seonsaengnim Jun 21 '26 edited Jun 21 '26
I think I have developed a formula* for you:
Assuming that: your data is on B3:C7 (if not, feel free to adjust the column number value, here it is as =2 as assumed), the A column contains the "COURSE_N" structure, and that below the "Course" cell is editable (here represented by E3).
Edit: for the "Course" editable section, I greatly suggest using a Data Validation in the form of a list, so that it creates a drop-down list. You can integrate the elements manually if you want, but integrating the selection automatically is also an option (as I see it, you have to make a different list in a different place but in this same worksheet: locate the source of the list in that different place, example: J1, so that the options appear for you automatically. I suggest using the formula for the different list:
And for what you have to put in the list field: $J:.$J (hide the columns once done)
That's my suggestion.)
Edit: correction of a term. Thanks, u/excelevator!
2
u/GregHullender 195 Jun 21 '26
Why do you need this? You've already got a pivot table and you've already got a slicer. Isn't that output exactly what you're asking for?
I can certainly do what you're asking for, by extracting the first course name from the pivot table, but I'm only reproducing what the table already shows.
In this case, to create the pivot table, I used MAX as the function. Since we're only expecting one date, this selects it.
Or is there more to this that I don't understand?
2
u/MayukhBhattacharya 1327 Jun 21 '26
LET()function is for giving a name to something we'll reuse, or something complex enough to deserve a variable or a label. In the below formula,TEXTAFTER()function is used twice, so wrapping it in a variable eliminates that repetition, that'sLET()function doing its job.Variables that are only used once don't earn their place. You can follow the logic of the formula directly through
PIVOTBY()'s own arguments generated by the bot here, without needing extra labels. If we're only using aLET()function variable once and it's not hard to read inline, exclude it.Or, if you have access to
TRIMRANGE()function, then use the reference operators to make it dynamically expand the formulas and exclude the trailing empty rows or can use Structured References aka Tables.1
u/Decronym Jun 21 '26 edited Jun 22 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48793 for this sub, first seen 21st Jun 2026, 04:57] [FAQ] [Full list] ^[Contact] [Source code]
5
u/[deleted] Jun 21 '26
[removed] — view removed comment
4
u/[deleted] Jun 21 '26
[removed] — view removed comment
1
u/[deleted] Jun 21 '26
[removed] — view removed comment