Unique, Textjoin, Filter all in one formula

[Georgiboy]

Hi all,

My first thread here i think..

I have been trying to get my head around how to do something with the new 365 functions, below i have used a seperate unique formula to generate a list of unique values from column A and then another formula to then use this unique list to populate a texjoin - filter formula.

Below is the two working together:

Book1
	A	B	C	D	E
1	1	a		1	a
2	2	b		2	b
3	3	c		3	c, d, e
4	3	d		4	f, g
5	3	e		5	h
6	4	f		6	i
7	4	g
8	5	h
9	6	i
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=UNIQUE(A1:A9)
E1:E6	E1	=TEXTJOIN(", ",TRUE,FILTER(B1:B9,A1:A9=D1))
Dynamic array formulas.

My question is: Is there a way to combine them both into one spill formula using something like HSTACK?

I want to learn how to use the unique list within the same formula it was created to pull out uniques and then process further with those uniques.

Sorry if this is easy, i have fried my brain today as i usually do everything with VBA but i am trying to broaden my knowledge.

Thanks for any advice offered

 

[Georgiboy]

I ahve figured out how to count the duplicates from column B but not to textjoin them.

Lambda-map examples.xlsx
	A	B	C	D	E
1	1	a		1	1
2	2	b		2	1
3	3	c		3	3
4	3	d		4	1
5	3	e		5	1
6	4	f
7	5	g
8
Sheet2

Cell Formulas
Range	Formula
D1:E5	D1	=LET(x,UNIQUE(A1:A7),HSTACK(x,MAP(x,LAMBDA(y,IFERROR(ROWS(UNIQUE(FILTER(B1:B7,(A1:A7=y)))),0)))))
Dynamic array formulas.

 

[Fluff]

How about

=LET(u,UNIQUE(A1:A9),HSTACK(u,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(B1:B100,A1:A100=br))))))

 

[JvdV]

Here is a link to a post I made a while back. It does show you an alternative using PowerQuery if you want to ever automate this aggregation.

 

[Georgiboy]

How about

=LET(u,UNIQUE(A1:A9),HSTACK(u,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(B1:B100,A1:A100=br))))))

Thanks Fluff, this helps with my understanding when joining functions together. This works really well.

I added a filter in with the unique part to prevent the formula from picking up blanks:

=LET(u,UNIQUE(FILTER(P2:P12,P2:P12<>"")),HSTACK(u,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(Q2:Q12,P2:P12=br))))))

Many thanks for this

 

[Georgiboy]

Here is a link to a post I made a while back. It does show you an alternative using PowerQuery if you want to ever automate this aggregation.

Hi JvdV,

Thanks for this, i will look into it as i do like PQ. I tend to use it when dealing with larger data sets and importing multiple files from folders.

I was looking for a formula in this instance as it has a very neat use within a dashboard.

Many thanks

George

 

[Georgiboy]

@Fluff With your formula and a fresh mind i managed to get the formula i was working on to do the same thing, for me though it is not about which formula to use it is about understanding what they are doing enough to trust using them.

Looking at the differences between both of the working formulae' it was only BYROW being swapped for MAP

=LET(x,P2:P13,u,UNIQUE(FILTER(x,x<>"")),HSTACK(u,MAP(u,LAMBDA(y,TEXTJOIN(", ",,FILTER(Q2:Q13,(x=y)))))))

Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.