Put multiple rows from FILTER horizontally on the same row

[christian79]

Hi all,

I am trying to get Excel to put results from the FILTER function on the same row as my query, as illustrated below:

Lookup table:

ID	DATE	CONTENT	DATE	CONTENT
1	Text1	Text2	Text3	Text4
1	Text5	Text6
2	Text7	Text8	Text9	Text10
2	Text11	Text12
2	Text13	Text14

Analysis table wanted result:

ID
1	Text1	Text2	Text3	Text4	Text5	Text6
2	Text7	Text8	Text9	Text10	Text11	Text12	Text13	Text14

Using FILTER returns the rows, but since this is a continuous list I get overflows when there are two or more matches. Is there a way to put the data horizontally, as in the example above?

Thankful for any tips/help.

Best regards,
Christian

 

[Fluff]

What is the filter formula you currently have?

 

[christian79]

It's just a very simple filter formula, like the one below.

=FILTER(SHEETNAME!$B$2:$G$100;SHEETNAME!$A$2:$A100=A1;"NOT FOUND")

 

[Fluff]

Thanks for that, how about

=TOROW(FILTER(SHEETNAME!$B$2:$G$100;SHEETNAME!$A$2:$A100=A1;"NOT FOUND"),1)

 

[christian79]

Thanks for that, how about

=TOROW(FILTER(SHEETNAME!$B$2:$G$100;SHEETNAME!$A$2:$A100=A1;"NOT FOUND"),1)

Yeah, thought about that one, but the translated formula names are complicating things. I have the Swedish language Office version, and can't find the translated version of that function (even though I'm on 365). My customer seems to enforce this language version as a company policy, but even so I'd imagine all functions to be available?

 

[JamesCanale]

I don't have all the latest 365 functions that make this easier. And I started tinkering before you put up your sample. But maybe some of this could be of use.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	DATE	CONTENT	DATE	CONTENT
2	1	Text1	Text2	Text3	Text4		1	Text1	Text2	Text3	Text4	Text5	Text6
3	1	Text5	Text6				2	Text7	Text8	Text9	Text10	Text11	Text12	Text13	Text14
4	2	Text7	Text8	Text9	Text10
5	2	Text11	Text12
6	2	Text13	Text14
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(A2:A6)
H2:M2,H3:O3	H2	=LET(a,FILTER($B$2:$E$6,$A$2:$A$6=G2),b,4*ROWS(a),c,INDEX(a,((SEQUENCE(1,b)-1)/4)+1,MOD(SEQUENCE(1,b)-1,4)+1),d,SUM(--(c<>0)),e,(c<>0)*SEQUENCE(1,b),f,SORT(TRANSPOSE(e)),g,INDEX(f,SEQUENCE(d,1,ROWS(f)-d+1)),TRANSPOSE(INDEX(c,1,g)))
Dynamic array formulas.

 

[Fluff]

No idea what TOCOL is in Swedish, but another option is

=LET(f,FILTER(Sheetname!$B$2:$G$100,Sheetname!$A$2:$A100=A2,"NOT FOUND"),s,SEQUENCE(,ROWS(f)*6,0),INDEX(f,INT(s/6)+1,MOD(s,6)+1))

 

[christian79]

No idea what TOCOL is in Swedish, but another option is

=LET(f,FILTER(Sheetname!$B$2:$G$100,Sheetname!$A$2:$A100=A2,"NOT FOUND"),s,SEQUENCE(,ROWS(f)*6,0),INDEX(f,INT(s/6)+1,MOD(s,6)+1))

This worked well, thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[christian79]

Thanks to JamesCanale as well.