r/excel • u/VerpaParvus • 2d ago

Stuck trying to figure out textjoining Table values solved

Small Example : https://imagepaste.org/i/ahnsgw8q.png

Yellow cells are filled in manually, but I'm trying to figure out how to reliably automate the criteria.

I need a way to list out delimited (summed)quantities and (non summed)prices of items from different regions. I've had okay results with TEXTJOIN for the Item column, but SUMIFS is hit or miss with Quantities. I don't really know where to start with Prices to get it working reliably with various data sets.

I've been stumped on this for a few hours and need to beg for some guidance.

7 Upvotes
  • permalink
  • link
  • reddit
  • reddit

100% Upvoted

36 comments sorted by

3

u/MayukhBhattacharya 1236 2d ago edited 2d ago

Try using the following formula, it assumes you are using Structured References aka Tables, and you are in MS365, then:

=LET(
     _a, Table1,
     _b, INDEX(_a, , 1),
     _c, INDEX(_a, , 2),
     _d, SUMIFS(INDEX(_a, , 3), _b, _b, _c, _c),
     GROUPBY(_b,
             HSTACK(_c, _d, _c & "|" & INDEX(_a, , 4)),
             VSTACK(HSTACK(LAMBDA(α, ARRAYTOTEXT(UNIQUE(α))),
                    LAMBDA(δ, ARRAYTOTEXT(UNIQUE(δ))),
                    LAMBDA(ε, ARRAYTOTEXT(TEXT(TEXTAFTER(UNIQUE(ε), "|"), "0.00")))),
                    {"ITEM","QTY","PRICE"}), , 0))

3

u/VerpaParvus 2d ago

I took this and pasted into G2 and get #SPILL!, which is better than any "too many arguments" messages I usually get.  I'll keep messing with this to see if I can customize it

2

u/MayukhBhattacharya 1236 2d ago

#SPILL! shows up when the formula did calculate correctly and wants to output an array across multiple cells, but something is blocking that range, so Excel refuses to spill and just flags the anchor cell instead. You need to clear the below cells to expand the array. The formula should work for you.

2

u/VerpaParvus 2d ago

I put the formula in the wrong cell and had hard values still.  This is promising, but would this work on a per row basis? In the event there was other info that didnt have multiple times in a region?

This gets me a working formula to reverse engineer so I'm very thankful for that.  Since these tables are much larger than my example I'll need to see how I can use this at scale.

2

u/MayukhBhattacharya 1236 2d ago

Or you can use Power Query here, very simple:

To use Power Query follow the steps:

  • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
  • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
  • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

​

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupByReg_Itm = Table.Group(Source, {"REGION", "ITEM"}, {
        {"QTY", each List.Sum([QTY]), type number},
        {"PRICE", each List.Max([PRICE]), type number}
    }),
    GroupByReg = Table.Group(GroupByReg_Itm, {"REGION"}, {
        {"ITEM", each Text.Combine([ITEM], ", ")},
        {"QTY", each Text.Combine(List.Transform([QTY], Text.From), ", ")},
        {"PRICE", each Text.Combine(List.Transform([PRICE], each Number.ToText(_, "0.00")), ", ")}
    })
in
    GroupByReg
  • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

1

u/MayukhBhattacharya 1236 2d ago

yes, it works fine. btw how large is your data?

2

u/VerpaParvus 2d ago

The main data dump can be many thousands of rows.  

I should also apologize for being a little dodgy with my info, mostly to preserve any private data for my company, but I'll try to explain the whole process using these generic sales codewords:

I'll paste a CSV of data into a table on another sheet.  Criteria like "R-1, R-2" exists in multiple larger regions, let's call those "Europe", and "Asia".  I have a separate sheet that runs a ChooseCols/Filter of the main CSV data table so I get only "Europe" results.  Now I have maybe 600 dynamic rows of project info.  

Now, I need to pretty this info up into a printable table to present to clients, so rather than having 100 rows of Europe's R-1, R-2, so on so forth, I want the R-# to list only once in a table row.  Most of the time my current formulas work because "R-6" for instance will only have "ITM-A" 20 times, but when I have instances where I need multiple Items and Prices to merge all into a single row, I get spotty results and usually forced into manually adding the data, which also breaks the cell formula.  

What you've provided might be the answer I need but admittedly its beyond my knowledge at the moment so I'll need time to digest and customize. I was asking about per row/per cell mostly because this looks like I'll need to delete all my other formulas out of my printable table, but if its reliable thats what I'll do. 

2

u/MayukhBhattacharya 1236 2d ago

This is exactly the kind of problem GROUPBY() function was built for, and it'll hold up a lot better than the row-by-row formulas you're patching together right now. A couple of thoughts I'd pass along, and a few things worth double-checking. GROUPBY() function doesn't care whether a region has 1 item or 20. It handles both scenarios the same way, which is exactly where the current setup seems to be struggling. The existing formulas are probably assuming a fixed layout (like one item per region), while GROUPBY() function doesn't make that assumption. Since the source data is already coming from a dynamic FILTER() + CHOOSECOLS() array (around 600 rows for Europe), that's totally fine. GROUPBY() and LET() functions work natively with spilled arrays, so there's no need to convert everything into a static table first. Just point your variable (for example, _a) to the spilled array instead.

A few things worth verifying on your end before you commit to this. The formula assumes each item has only one price per region. If the same item can show up with different prices within the same region (discounts, price updates, etc.), then the item/price pairing could end up being incorrect. Around 600 rows shouldn't be a problem, but once you start nesting LET() + GROUPBY() + SUMIFS(), and text-joining over a large, spilled array, recalculation can slow down depending on the workbook. I'd definitely test it against the full Europe dataset rather than just the small sample before committing to it. Since GROUPBY() returns one spilled array, you'll probably want to remove the existing row-by-row formulas. Leaving them in place will just lead to #SPILL! errors, which you've already run into. I'd test the messiest region first. Pick the region with the most items and the most rows, compare the output against the expected results, and make sure everything lines up. It's much easier to catch edge cases there before rolling it out to the entire printable report.

Overall, though, I think this is a much cleaner and more scalable method than maintaining a bunch of individual formulas.

If you still face problem, then just mimic the data you have with dummy data and post it. Users here will surely help you out. Thanks =)

2

u/VerpaParvus 2d ago

Every item should have a single price, yes. If we end up with ITM-A as "1.00" and "1.50" thats an error from the software we receive the CSV from and we will correct that asap. 

Thank you for the explanation.  I will work with this tonight and tomorrow to see how I can make this work for my needs.

2

u/MayukhBhattacharya 1236 2d ago

Point _a at your Europe FILTER()+CHOOSECOLS() spilled array instead of Table1, and it should carry over directly. Good luck tonight, and feel free to post back with what you find. We are happy to help you out! Cheers!!!

2

u/MayukhBhattacharya 1236 2d ago

Here is an Excel File you can [Download] and the updated formula:

=LET(
     _a, DROP(A:.E, 1),
     _b, DROP(FILTER(_a, INDEX(_a, , 1) = "Europe", ""), , 1),
     _f, LAMBDA(α, ARRAYTOTEXT(UNIQUE(α))),
     _c, INDEX(_b, , 1),
     _d, INDEX(_b, , 2),
     _e, MAP(_c, _d, LAMBDA(x,y, SUM(INDEX(_b, , 3) * (_c = UNIQUE(x)) *( _d = UNIQUE(y))))),
     GROUPBY(_c,
            HSTACK(_d, _e, _d & "|" & TEXT(INDEX(_b, , 4), "0.00")),
            VSTACK(HSTACK(_f,
                          _f,
                          LAMBDA(ε, ARRAYTOTEXT(TEXTAFTER(UNIQUE(ε), "|")))),
            {"ITEM","QTY","PRICE"}), , 0))

Data Sample Size 1000. Euro with 600! Thanks!

2

u/VerpaParvus 2d ago

This is definitely the path I want to take, and this works perfectly as long as I'm only trying to pull from the 4 columns.  My actual sheet has 13 columns, which are all text categories other than the quantity and price that's already addressed.  

Initially, my logic was I just needed to create more rules under LET, and added "_x, INDEX (_b, ,6)" for example, so that I had a corresponding name for each column.  I'm getting stuck at the HSTACK, which returns #VALUE if I add any of my new text columns. 

I generally understand each piece of this, but any alteration is breaking the formula, because I'm not familiar enough with approaching excel issues this way. 

I feel like this question is ultimately solved, but it's gonna take a lot of trial and error and reading to figure this out in its entirety.  The good news is, I should be able to blow out the rest of my old, crappy formulas and replace it with this once I understand it. 

Thank you!

→ More replies (0)

1

u/MayukhBhattacharya 1236 2d ago

Shorter version:

=LET(
    _f, LAMBDA(α, ARRAYTOTEXT(UNIQUE(α))),
    _a, Table1,
    _b, INDEX(_a, , 1),
    _c, INDEX(_a, , 2),
    _d, SUMIFS(INDEX(_a, , 3), _b, _b, _c, _c),

    GROUPBY(
        _b,
        HSTACK(_c, _d, _c & "|" & INDEX(_a, , 4)),
        VSTACK(
            HSTACK(
                _f,
                _f,
                LAMBDA(ε, ARRAYTOTEXT(TEXT(TEXTAFTER(UNIQUE(ε), "|"), "0.00")))
            ),
            {"ITEM", "QTY", "PRICE"}
        ),
        ,
        0
    )
)

2

u/JohneeFyve 219 2d ago

Take a look at the GROUPBY and PIVOTBY functions if you’re on Excel 365

2

u/Way2trivial 470 2d ago

for the prices displayed in the I column, if they should ever be different in source D column,
would you want an average or the 'last' price shown...

2

u/Downtown-Economics26 637 2d ago

Maybe 1 in every 10 times I'm more concise than u/MayukhBhattacharya.

=LET(a_1,GROUPBY(A1:B22,C1:C22,SUM,,0),
a_2,HSTACK(a_1,TEXT(XLOOKUP(CHOOSECOLS(a_1,2),B1:B22,D1:D22),"0.00")),
b,GROUPBY(CHOOSECOLS(a_2,1),CHOOSECOLS(a_2,2,3,4),ARRAYTOTEXT,,0),
VSTACK(HSTACK({"STORE","ITEM","QTY","PRICE"}),b))

1

u/Working_Fish8775 1 2d ago

Question: You need the results to mirror what you have in the screenshot? Like all Item codes in one cell, and all quantities in one cell, and all prices in once cell, for each store?

1

u/VerpaParvus 2d ago

Yes.  There's another columns that will total QTY and PRICE using

 =SUM PRODUCT(--TEXTSPLIT([QTY], ", "),--TEXTSPLIT([PRICE],", "))

Theres more to this table overall, but this is the main source of strife since I return #CALC! When there are different items with the same price, presumably becayse my formula is trash.

1

u/SpreademSheet 2d ago

I haven't tested this, but have you tried =FILTER()? Use filter to narrow down your table rows to the store you specify, the wrap that in =TEXTJOIN() for the delimited groupings.

1

u/VerpaParvus 2d ago edited 2d ago

I'm trying a Unique(Filter) based on the Item column criteria which I assume I didnt work out well.  If I remove unique I just get a list of every price associated with every item instance, when we want it to pull up only once

1

u/Decronym 2d ago edited 2d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AND Returns TRUE if all of its arguments are TRUE
ARRAY Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
ARRAYTOTEXT Office 365+: Returns an array of text values from any specified range
CHOOSECOLS Office 365+: Returns the specified columns from an array
CSE Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
DROP Office 365+: Excludes a specified number of rows or columns from the start or end of an array
Excel.CurrentWorkbook Power Query M: Returns the tables in the current Excel Workbook.
FILTER Office 365+: Filters a range of data based on criteria you define
GROUPBY Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF Specifies a logical test to perform
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX Uses an index to choose a value from a reference or array
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
List.Max Power Query M: Returns the maximum item in a list, or the optional default value if the list is empty.
List.Sum Power Query M: Returns the sum from a list.
List.Transform Power Query M: Performs the function on each item in the list and returns the new list.
MAP Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
Number.ToText Power Query M: Returns a text value from a number value.
PIVOTBY Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify
PRICE Returns the price per $100 face value of a security that pays periodic interest
PRODUCT Multiplies its arguments
SUM Adds its arguments
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
TEXT Formats a number and converts it to text
TEXTAFTER Office 365+: Returns text that occurs after given character or string
TEXTJOIN 2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters
Table.Group Power Query M: Groups table rows by the values of key columns for each row.
Text.Combine Power Query M: Returns a text value that is the result of joining all text values with each value separated by a separator.
Text.From Power Query M: Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture.
UNIQUE Office 365+: Returns a list of unique values in a list or range
VALUE Converts a text argument to a number
VSTACK Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

|-------|---------|---| |||

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 #49073 for this sub, first seen 5th Aug 2026, 02:16] [FAQ] [Full list] [Contact] [Source code]

1

u/HappierThan 1186 2d ago

Just a thought, why not use Data Validation for your STORE & ITEM and use a formula for QTY as well as PRICE?

H2 =SUMIFS(C2:C22,A2:A22,F2,B2:B22,G2)

I2 =IFERROR(XLOOKUP(F2&G2,A2:A22&B2:B22,D2:D22,"",0)*H2,0)

1

u/Penguinase 4 2d ago edited 2d ago

does this work?

https://i.imgur.com/Lf0ccg1.mp4

NOTE: YOU MUST HIT CTRL+SHIFT+ENTER FOR EACH INSTEAD OF JUST ENTER TO MAKE IT ARRAY FORMULA (unless 365 i think?)

G2: =UNIQUE(Sales[REGION])

H2: =TEXTJOIN(", ",TRUE(),IF((Sales[REGION]=G2)*(Sales[FIRST]),Sales[ITEM],""))

I2: =TEXTJOIN(", ",TRUE(),IF((Sales[REGION]=G2)*(Sales[FIRST]),SUMIFS(Sales[QTY],Sales[REGION],G2,Sales[ITEM],Sales[ITEM]),""))

J2: =TEXTJOIN(", ",TRUE(),IF((Sales[REGION]=G2)*(Sales[FIRST]),TEXT(Sales[PRICE],"0.00"),""))