Filter/Transpose a multiple rows and multiple columns in a same row.

[mmr1]

Hi,


Required a formula help for Filter/Transpose a multiple rows and multiple columns in a same row as per condition met with row headers . Row Headers that contain duplicate row headers and reorder all values or text values from multiple columns into one row.

solution to be needed in Column H to L.


Thanks for the help.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1	Months	Week-1	Week-2	Week-3	Week-4			Months	Week-1	Week-2	Week-3	Week-4
2	Jan-20	32500
3	Jan-20		9854
4	Jan-20				12365
5	Feb-20	542
6	Feb-20			1254
7	Feb-20
8	Mar-20		8745
9	Mar-20	4569
10	Mar-20				2541
11	Mar-20
12	Apr-20		5452	5256
13	Apr-20				12136
14	Apr-20	2564
15	May-20
16	May-20
17	May-20			No Values
18	May-20		5465
19
20
Sheet1

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Months	Week-1	Week-2	Week-3	Week-4			Months	Week-1	Week-2	Week-3	Week-4
2	01/01/2020	32500						01/01/2020	32500	9854		12365
3	01/01/2020		9854					01/02/2020	542		1254
4	01/01/2020				12365			01/03/2020	4569	8745		2541
5	01/02/2020	542						01/04/2020	2564	5452	5256	12136
6	01/02/2020			1254				01/05/2020		5465	No Values
7	01/02/2020
8	01/03/2020		8745
9	01/03/2020	4569
10	01/03/2020				2541
11	01/03/2020
12	01/04/2020		5452	5256
13	01/04/2020				12136
14	01/04/2020	2564
15	01/05/2020
16	01/05/2020
17	01/05/2020			No Values
18	01/05/2020		5465
Report

Cell Formulas
Range	Formula
H2:H6	H2	=UNIQUE(A2:A18)
I2:L6	I2	=FILTER(FILTER(FILTER($B$2:$E$18,$A$2:$A$18=$H2),$B$1:$E$1=I$1),FILTER(FILTER($B$2:$E$18,$A$2:$A$18=$H2),$B$1:$E$1=I$1)<>"","")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
abcd	=Report!$B$2:$E$18	I2:L6

 

[sandy666]

another option with Power Query then Pivot Table

Months	Week-1	Week-2	Week-3	Week-4		_Value	Attribute
01/01/2020	32500					Months	Week-1	Week-2	Week-3	Week-4
01/01/2020		9854				01/01/2020	32500	9854		12365
01/01/2020				12365		01/02/2020	542		1254
01/02/2020	542					01/03/2020	4569	8745		2541
01/02/2020			1254			01/04/2020	2564	5452	5256	12136
01/02/2020						01/05/2020		5465	0
01/03/2020		8745
01/03/2020	4569
01/03/2020				2541
01/03/2020
01/04/2020		5452	5256
01/04/2020				12136
01/04/2020	2564
01/05/2020
01/05/2020
01/05/2020			No Values
01/05/2020		5465

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Date = Table.TransformColumnTypes(Source,{{"Months", type date}}),
    UOC = Table.UnpivotOtherColumns(Date, {"Months"}, "Attribute", "Value")
in
    UOC

 

[mmr1]

Many thanks to all of you for amazing solution.

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day

 

[Fluff]

Glad to help & thanks for the feedback.