Hi. I am looking for an excel formular to filter the data with columns heading a-g if columns heading dog to house has "new" in the cell.

[fayo2009]

a	b	c	d	e	f	g		dog	cat	rat	nose	house	Output
123	123	123	123	123	123	123					new		126​	126​	126​	126​	126​	126​	126​
124	124	124	124	124	124	124				new			133​	133​	133​	133​	133​	133​	133​
125	125	125	125	125	125	125							138​	138​	138​	138​	138​	138​	138​
126	126	126	126	126	126	126		new
127	127	127	127	127	127	127
128	128	128	128	128	128	128			new
129	129	129	129	129	129	129
130	130	130	130	130	130	130						new	See formular below
131	131	131	131	131	131	131				new			FILTER(A4:G22,INDEX(I4:M22,,MATCH("dog",I3:M3,0))="new")
132	132	132	132	132	132	132
133	133	133	133	133	133	133		new				new
134	134	134	134	134	134	134							Looking for a formular that would look at all the values in cells I3-M3
135	135	135	135	135	135	135			new
136	136	136	136	136	136	136
137	137	137	137	137	137	137
138	138	138	138	138	138	138		new
139	139	139	139	139	139	139
140	140	140	140	140	140	140						new

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you mean

=FILTER(A4:G22,BYROW(I4:M22,LAMBDA(br,COUNTIFS(br,"New"))))

 

[fayo2009]

Hi & welcome to MrExcel.
Is this what you mean

=FILTER(A4:G22,BYROW(I4:M22,LAMBDA(br,COUNTIFS(br,"New"))))

Thanks for yor quick response, much appreciated. I am getting #NAME? error. What is "br" in the formular?

 

[Joe4]

Which version of Excel are you using? Certain functions may not be available to you depending on which version of Excel you are using.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[fayo2009]

Which version of Excel are you using? Certain functions may not be available to you depending on which version of Excel you are using.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Microsoft 365. Thanks

 

[Fluff]

If you don't have the Lambda functions yet, try

=FILTER(A4:G22,MMULT(--(I4:M22="new"),SEQUENCE(COLUMNS(I4:M22),,,0)))

 

[Joe4]

I have 365 also and Fluff's formula works for me.
Perhaps you are not current on all your updates, and do not have the LAMBDA function yet.
Can you confirm if this function is available to you?

See here (specifically, the section that talks about the #NAME! error): Excel LAMBDA function: how to write, calculate and use.

 

[fayo2009]

Microsoft 365. Thanks

i dont have BYROW and LAMBDA

 

[Fluff]

i dont have BYROW and LAMBDA

In that case see post#6

 

[fayo2009]

I have 365 also and Fluff's formula works for me.
Perhaps you are not current on all your updates, and do not have the LAMBDA function yet.
Can you confirm if this function is available to you?

See here (specifically, the section that talks about the #NAME! error): Excel LAMBDA function: how to write, calculate and use.

Thanks! my home computer has BYCOL and LAMBDA and it works.

,

If you don't have the Lambda functions yet, try

=FILTER(A4:G22,MMULT(--(I4:M22="new"),SEQUENCE(COLUMNS(I4:M22),,,0)))

If you don't have the Lambda functions yet, try

=FILTER(A4:G22,MMULT(--(I4:M22="new"),SEQUENCE(COLUMNS(I4:M22),,,0)))[
[/QUOTE]
 Perfect!! it works. Thank you so much. Is it possible to duplicate the entry if "new" is in both nose and house and is there a formular transpose column I3-M3 (dog- house against the filtered result.