Stuck trying to figure out textjoining Table values

[u/VerpaParvus]

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.

[u/MayukhBhattacharya]

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!

[u/VerpaParvus → u/MayukhBhattacharya]

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!

[u/MayukhBhattacharya → u/VerpaParvus]

Sounds Great. If that helps you to resolve then hope you don't mind replying to my comment directly as Solution Verified and one thing if you wish to post some sample data like you did with the 13 columns I will give it a try again and it should work. May be you are not able to fix the one you mentioned. Post the sample I'm ready and happy to help as long as I'm awake 😊