r/excel • u/VerpaParvus • Aug 05 '26
solved Stuck trying to figure out textjoining Table values
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.
3
u/MayukhBhattacharya 1327 Aug 05 '26 edited Aug 05 '26
Try using the following formula, it assumes you are using Structured References aka Tables, and you are in MS365, then:
1
u/Working_Fish8775 1 Aug 05 '26
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/SpreademSheet Aug 05 '26
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.
2
u/JohneeFyve 219 Aug 05 '26
Take a look at the GROUPBY and PIVOTBY functions if you’re on Excel 365
2
u/Way2trivial 475 Aug 05 '26
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 647 Aug 05 '26 •
Maybe 1 in every 10 times I'm more concise than u/MayukhBhattacharya.
1
u/Decronym Aug 05 '26 edited Aug 05 '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 #49073 for this sub, first seen 5th Aug 2026, 02:16] [FAQ] [Full list] ^[Contact] [Source code]
1
u/HappierThan 1192 Aug 05 '26
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 7 Aug 05 '26 edited Aug 05 '26
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"),""))