• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in Textjoin Formula along with Count

G

Ganesh Babi

New Member
Please assist me in sorting the below excel query and the expected output is showin in the below table.
SellerProductProductCurrent OutputExcepting Output is
SallyOrangesOrangesSally, AdamSally -1, Adam-1
RobertApricotsApricotsRobert, SallyRobert -1, Sally-2
AdamBananas=TEXTJOIN(", ",TRUE,UNIQUE(IF($B$2:$B$12=$D2,$A$2:$A$12,"")))
AdamApples
RobertLemons
SallyApricots
SallyApricots
AdamOranges
RobertBananas
AdamLemons
SallyApples
 
I looked at this and didn't reply because my solution was so convoluted that I thought there must be a more elegant formula (I still do!).
@mohamed ilyas' solution made me think that my formula wasn't so bad!
So in the attached, on Sheet1 there's a single-celled formula at cell D2 (with hints for the arguments):

1727276737726.png

This is a named lambda formula (FruitSummary) which can be found in Name Manager.
The development version of this formula is in cell G2 (which could be used if you didn't want to have a named formula), but this is for interest only and can be deleted.

Separately from all that, there's a Power Query solution on sheet PQ:
1727276920118.png

The query result (green table) is based on the grey table to the left. After adjusting/amending/adding to the grey table, the green table needs updating by right-clicking the green table and choosing Refresh.
 

Attachments

  • Chandoo57754.xlsx
    20.4 KB · Views: 4
I really appreciate your help. I didn't think beyond my boundaries. Let me review the excel sheet and come back to you. Can we simply the formula for better understanding
 
p45cal said:
I looked at this and didn't reply because my solution was so convoluted that I thought there must be a more elegant formula (I still do!).
@mohamed ilyas' solution made me think that my formula wasn't so bad!
So in the attached, on Sheet1 there's a single-celled formula at cell D2 (with hints for the arguments):

View attachment 88450

This is a named lambda formula (FruitSummary) which can be found in Name Manager.
The development version of this formula is in cell G2 (which could be used if you didn't want to have a named formula), but this is for interest only and can be deleted.

Separately from all that, there's a Power Query solution on sheet PQ:
View attachment 88451

The query result (green table) is based on the grey table to the left. After adjusting/amending/adding to the grey table, the green table needs updating by right-clicking the green table and choosing Refresh.
Click to expand...
I have trouble understanding
Code: 
= Table.AddColumn(#"Grouped Rows", "Result", each fnStuff([grp]))
Especially the
Code: 
each fnStuff([grp])
part. Could you please explain? Thx
 
pecoflyer said:
I have trouble understanding
Code: 
= Table.AddColumn(#"Grouped Rows", "Result", each fnStuff([grp]))
Especially the
Code: 
each fnStuff([grp])
part. Could you please explain? Thx
Click to expand...
fnStuff is my lack of imagination when naming a function!
In the attached, I've added a second group of queries called develop function.
In it I've shown 2 versions of the query fnStuff (2) and fnStuff (3).
fnStuff (2) is the function converted (back) into a plain query
fnStuff (3) is the same except I've split single steps in fnStuff (2) to multiple smaller steps so that it's easier to follow what's happening.

Both of these functions (actually queries) act on ResultTable2 which is just a duplicate of the first 2 steps of ResultTable query with one of the tables in the grp column (Apricot row) clicked on to 'drill down' it to create a sample table for the other queries to work on.

Looking at the differences in the MCode of the function fnStuff and the query fnStuff (2)
The normal query's first 2 lines:
Code: 
let
    Source = ResultTable2,
The function's first 2 lines:
Code: 
(tbl)=>let
    Source = tbl,
Here, tbl is being passed as an argument.
 

Attachments

  • Chandoo57754_v2.xlsx
    22.3 KB · Views: 1
Last edited:
p45cal said:
I looked at this and didn't reply because my solution was so convoluted that I thought there must be a more elegant formula (I still do!).
@mohamed ilyas' solution made me think that my formula wasn't so bad!
So in the attached, on Sheet1 there's a single-celled formula at cell D2 (with hints for the arguments):

View attachment 88450

This is a named lambda formula (FruitSummary) which can be found in Name Manager.
The development version of this formula is in cell G2 (which could be used if you didn't want to have a named formula), but this is for interest only and can be deleted.

Separately from all that, there's a Power Query solution on sheet PQ:
View attachment 88451

The query result (green table) is based on the grey table to the left. After adjusting/amending/adding to the grey table, the green table needs updating by right-clicking the green table and choosing Refresh.
Click to expand...
Nice solutions!
Who says Power Query offers a code-free solution? That is pretty sophisticated stuff; especially the use of the function. My 'standard' Excel formula solution used GROUPBY because it is available on my Insider version.
Code: 
= LET(
    sellerInst,  Seller & "-" & COUNTIFS(Seller, Seller, Product, Product),
    GROUPBY(Product, sellerInst, DISTINCTλ,,0)
  )
 
"where"
 
DISTINCTλ
= LAMBDA(t, TEXTJOIN(", ",,UNIQUE(t)));
I like your use of the Lambda function; I could argue the case that every formula should be expressed as a Lambda function in order to make its intent clear even while partially hiding the detail.
 
You must log in or register to reply here.
Back
Top