Insert sequence number and embed it to my data

I have this data:

A B
1 X BIRD
2 X LION
3 Y FISH
4 Z CHICKEN
5 X DUCK
6 Y SNAKE
7 X TIGER
8 X CROCODILE
9 Z ELEPHANT
10 Z DRAGON
11 Z WORM

I use this formula:

``````=index(
trim( split( transpose(query ( QUERY ( FILTER ( {A1:A&"@@" , B1:B&"," }  , A1:A<>"") ,"select max(Col2) group by Col2 pivot Col1" ), , 100)), "@@"))
)
``````

and this is what I got as expected :

but I want to do some modification : I want to insert sequence numbers in front of every animal items (replacing the comma) like this :

How to modify my formula to get this result ?

I would use the MAP function instead of QUERY.

``````=MAP(TOCOL(UNIQUE(A:A),1), LAMBDA(r, {r,
JOIN(" ",
LET(v, SORT(FILTER(B:B, A:A=r)),
INDEX(SEQUENCE(ROWS(v)) & "-" & v))) & ","}))
``````

Your original formula can be reduced by roughly half using the same approach:

``````=MAP(TOCOL(UNIQUE(A:A),1), LAMBDA(r,
{r, JOIN(", ", SORT(FILTER(B:B, A:A=r))) &","}))
``````

• Thanks for the answer. Using your code , I'm switching all my 'long' query() to this less complicated map(). Really helpful. Commented Sep 5 at 9:42