Combining Column Values depending on the Date in the first row

[mazher]

I will be extremely thankful, if someone can help me as I have no idea how It can be done. I am usinh Excel 365.

I Sheet 1 I have dates in column A1:AIC1 (duplicate dates) and underneath I have invoice numbers

In Sheet 2, I have used this formula in cell A1 =TRANSPOSE(UNIQUE(TRANSPOSE(UNIQUE(Sheet2!A1:AIC1))))

I have no udea why TRANSPOSE(UNIQUE(Sheet2!A1:AIC1)) was not working have to use unique and transpose again to get a unique list of dates.

I need help for a formula to stack all the invoice numbers from Sheet 1 with the same date under the Unique Date in row 1 of Sheet 2

Thanks in advance.

 

[Fluff]

How about

=UNIQUE(Sheet2!A1:AIC1,1)

and

=TOCOL(FILTER(Sheet2!$A$2:$AIC$2,Sheet2!$A$1:$AIC$1=A1),1)

 

[mazher]

Thanks @Fluff for your reply.

I got the unique dates by your formula.

Unfortunately I was unable to make this working =TOCOL(FILTER(Sheet2!$A$2:$AIC$2,Sheet2!$A$1:$AIC$1=A1),1)

Here is my sample data. I have just added it on one sheet to make it a bit simple. In actual my data is in the Sheet 1 and need the result in Sheet 2 , When stacking the data for each date , I dont want the empty cells)

27/04/2024​	27/04/2024​	28/04/2024​	28/04/2024​	29/04/2024​	29/04/2024​	29/04/2024​	30/04/2024​
A	E	B	L	F	Q	U	X
B	F	C	M	G	R	V	Y
C	G	I	N	N	S	W	Z
D	H	J	O	O	T		A
		K	P				B

below is the expected result

27/04/2024​	28/04/2024​	29/04/2024​	30/04/2024​
A	B	F	X
B	C	G	Y
C	I	N	Z
D	J	O	A
E	K	Q	B
F	L	R
G	M	S
H	N	T
	O	U
	P	V
		W

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	27/04/2024	28/04/2024	29/04/2024	30/04/2024
2	A	B	F	X
3	E	L	Q	Y
4	B	C	U	Z
5	F	M	G	A
6	C	I	R	B
7	G	N	V
8	D	J	N
9	H	O	S
10		K	W
11		P	O
12			T
Sheet2

Cell Formulas
Range	Formula
A1:D1	A1	=UNIQUE(TOROW(Sheet1!A1:AIC1,1),1)
A2:A9,D2:D6,C2:C12,B2:B11	A2	=TOCOL(FILTER(Sheet1!$A$2:$AIC$10,Sheet1!$A$1:$AIC$1=A1),1)
Dynamic array formulas.