Alternative to using Aggregate

[rex759]

Hello,
I’ve been using a combination of Index, Aggregate and Countif to show the Nth occurrence, works great.

I was reading about the LAMBDA function and was attempting to build this formula into it. However, I thought it might be easier if I found a shorter option to the Aggregate formula.

The filter formula seems like a good option but I cant get past the spill over. How do I get H6:H14 to populate? Maybe its not meant to work the way I need it to.

Any advice is appreciated

Book1
	A	B	C	D	E	F	G	H
1	Master Sheet			Category	ID		Category	ID
2	a	101		a	101		a	101
3	b	102		a	105		a	105
4	c	103		a	109		a	109
5	d	104		a	113		a	113
6	a	105		c	103		c
7	b	106		c	107		c
8	c	107		c	111		c
9	d	108		d	104		d
10	a	109		d	108		d
11	b	110		b	102		b
12	c	111		b	106		b
13	d	112		b	110		b
14	a	113		b	114		b
15	b	114
16	c	115
17	d	116
18
Sheet7

Cell Formulas
Range	Formula
H2	H2	=INDEX(FILTER(B2:B17,A2:A17=G2),)
E2:E14	E2	=INDEX($B$2:$B$17,AGGREGATE(15,6,(ROW($A$2:$A$17)-ROW($A$2)+1)/($A$2:$A$17=$D2),COUNTIF($D$2:D2,$D2)))

 

[shaowu459]

工作簿1
	A	B	C	D	E	F	G
1	Master Sheet			Category	ID
2	a	101		a	101
3	b	102		a	105
4	c	103		a	109
5	d	104		a	113
6	a	105		c	103
7	b	106		c	107
8	c	107		c	111
9	d	108		d	104
10	a	109		d	108
11	b	110		b	102
12	c	111		b	106
13	d	112		b	110
14	a	113		b	114
15	b	114
16	c	115
17	d	116
18
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=MAP(D2:D14,LAMBDA(x,INDEX(FILTER(B2:B17,A2:A17=x),COUNTIF(D2:x,x))))
Dynamic array formulas.

 

[Skybluekid]

Try the below:

Book1
	A	B	C	D	E	F	G	H
1	Master Sheet			Category	ID		Category	ID
2	a	101		a	101		a	101 ,105 ,109 ,113
3	b	102		a	105		b	102 ,106 ,110 ,114
4	c	103		a	109		c	103 ,107 ,111 ,115
5	d	104		a	113		d	104 ,108 ,112 ,116
6	a	105		c	103
7	b	106		c	107
8	c	107		c	111
9	d	108		d	104
10	a	109		d	108
11	b	110		b	102
12	c	111		b	106
13	d	112		b	110
14	a	113		b	114
15	b	114
16	c	115
17	d	116
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(A2:A17)
H2:H5	H2	=TEXTJOIN(" ,",,FILTER($B$2:$B$17,$A$2:$A$17=G2))
Dynamic array formulas.

 

[shaowu459]

if you need result in post #3, you can also try GROUPBY:

工作簿1
	A	B	C	D	E	F	G
1	Master Sheet				Category	ID
2	a	101			a	101, 105, 109, 113
3	b	102			b	102, 106, 110, 114
4	c	103			c	103, 107, 111, 115
5	d	104			d	104, 108, 112, 116
6	a	105
7	b	106
8	c	107
9	d	108
10	a	109
11	b	110
12	c	111
13	d	112
14	a	113
15	b	114
16	c	115
17	d	116
18
19
Sheet1

Cell Formulas
Range	Formula
E2:F5	E2	=GROUPBY(A2:A17,B2:B17,ARRAYTOTEXT,,0)
Dynamic array formulas.

 

[kvsrinivasamurthy]

Try

=SORT(A2:B17,1,1,0)

 

[kvsrinivasamurthy]

Another method

=BYROW(G2:G14,LAMBDA(r,INDEX(FILTER(B2:B17,A2:A17=r),COUNTIF(G2:r,r))))

 

[kvsrinivasamurthy]

post deleted.

 

[rex759]

Wow! So many different options. The Map and Byrow are new to me and very cool. I'll keep the other examples as well as I can see future needs for them. Always more to learn with Excel. Thank you very much!

 

[shaowu459]

Wow! So many different options. The Map and Byrow are new to me and very cool. I'll keep the other examples as well as I can see future needs for them. Always more to learn with Excel. Thank you very much!

thanks for your feedback