Insert sequence number and embed it to my data

Question

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 ?

Answer 1

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))) & ","}))

FILTER  INDEX  JOIN  LAMBDA  LET  MAP  ROWS  SEQUENCE  SORT  TOCOL  UNIQUE

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))) &","}))

FILTER  JOIN  LAMBDA  MAP  SORT  TOCOL  UNIQUE