Rows to Column - Macro

[wardex]

Hi guys, can you help me. I need a macro for this problem. Thanks in advance!

 

[Georgiboy]

Here is a long winded formula to do the same:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	1	a	b	c	d		1	a	b	c	d	e	f	g	h	i	j	k	l
2		e	f	g	h		2	aa	bb	cc	dd	ee	ff	gg	hh
3		i	j	k	l		3	aaa	bbb	ccc	ddd	eee	fff	ggg	hhh	iii	jjj	kkk	lll	mmm	nnn	ooo	ppp	qqq	rrr	sss	ttt
4	2	aa	bb	cc	dd
5		ee	ff	gg	hh
6	3	aaa	bbb	ccc	ddd
7		eee	fff	ggg	hhh
8		iii	jjj	kkk	lll
9		mmm	nnn	ooo	ppp
10		qqq	rrr	sss	ttt
Sheet1

Cell Formulas
Range	Formula
G1:AA3	G1	=LET( r, A1:E10, s, SCAN(1,INDEX(r,,1),LAMBDA(a,x,IF(x="",a,x))), m, BYROW(UNIQUE(s),LAMBDA(x,TEXTJOIN(",",,FILTER(DROP(r,,1),s=x)))), c, MAX(BYROW(m,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1))), xml, "<t><s>" & SUBSTITUTE(m,",","</s><s>") & "</s></t>", HSTACK(UNIQUE(s),IFERROR(FILTERXML(xml,"//s[" & SEQUENCE(,c) & "]"),"")) )
Dynamic array formulas.

 

[wardex]

Hi sir ZOT hoping you're still there.

Regarding with the code which you wrote, can you help me modify a little bit.

Actually it was perfect code that I need but I encountered error if a cells in column A contains text / letters, there will be an error prompting please see attached.

Thanks