Nested Filter Function - it doesnt return the right columns

[Andre Pereira]

Hello all,

Before asking you for help on the subject, i perform a quick search for something similar and i didnt find no thread if theres one apologize and i'll remove this.

So, i wanted to perform a nested Filter Function because i have a dataset where i have information that i want to retrieve but i dont want all the columns, making a nested filter seems pretty simple but it keeps returning me rows instead of columns.

Array: G10:J25
Condition Range: H10:H25
COndition: 26

Wanted columns 1 (G) and 4 (J)

Formula looks like this:

=FILTER(FILTER($G$10:$J$25;$H$10:$H$25=P6);{1;0;0;1})

what i am doing wrong? i tried also with INDEX instead outer Filter but keeps returning me rows instead of columns.

Thanks in advance for your time

AndreP

 

[dreid1011]

There might be a way to get it into a single formula, but I don't have enough time at work now to play with it, but perhaps this for the moment:

Edit: I take it back, it was relatively simple, try this instead of the formulas at the bottom:

=HSTACK(TAKE(FILTER(G10:J25,H10:H25=P6),,1),TAKE(FILTER(G10:J25,H10:H25=P6),,-1))

Book1
	G	H	I	J	K	L	M
9	Sample	Color	Size	Value		Sample	Value
10	96	Green	22	846.6		15	101.98
11	89	Yellow	26	744.66		46	619.97
12	15	Blue	36	101.98		44	695.32
13	21	Orange	37	1197.71		23	1338.41
14	50	Green	27	851.48
15	42	Yellow	27	408.09
16	46	Blue	20	619.97
17	42	Orange	24	601.52
18	49	Green	25	1263.62
19	85	Yellow	33	665.92
20	44	Blue	27	695.32
21	60	Orange	24	487.7
22	70	Green	30	215.58
23	67	Yellow	30	353.14
24	23	Blue	30	1338.41
25	71	Orange	28	1082.57
Sheet3

Cell Formulas
Range	Formula
L10:L13	L10	=TAKE(FILTER(G10:J25,H10:H25="Blue"),,1)
M10:M13	M10	=TAKE(FILTER(G10:J25,H10:H25="Blue"),,-1)
Dynamic array formulas.

 

[Andre Pereira]

There might be a way to get it into a single formula, but I don't have enough time at work now to play with it, but perhaps this for the moment:

Edit: I take it back, it was relatively simple, try this instead of the formulas at the bottom:

=HSTACK(TAKE(FILTER(G10:J25,H10:H25=P6),,1),TAKE(FILTER(G10:J25,H10:H25=P6),,-1))

Book1
	G	H	I	J	K	L	M
9	Sample	Color	Size	Value		Sample	Value
10	96	Green	22	846.6		15	101.98
11	89	Yellow	26	744.66		46	619.97
12	15	Blue	36	101.98		44	695.32
13	21	Orange	37	1197.71		23	1338.41
14	50	Green	27	851.48
15	42	Yellow	27	408.09
16	46	Blue	20	619.97
17	42	Orange	24	601.52
18	49	Green	25	1263.62
19	85	Yellow	33	665.92
20	44	Blue	27	695.32
21	60	Orange	24	487.7
22	70	Green	30	215.58
23	67	Yellow	30	353.14
24	23	Blue	30	1338.41
25	71	Orange	28	1082.57
Sheet3

Cell Formulas
Range	Formula
L10:L13	L10	=TAKE(FILTER(G10:J25,H10:H25="Blue"),,1)
M10:M13	M10	=TAKE(FILTER(G10:J25,H10:H25="Blue"),,-1)
Dynamic array formulas.

First of all, many thanks for your reply.

The formula worked but if i get it right, the formula TAKE (positive) gets columns from left to right and the formula TAKE (negative) gets columns from right to left, however if you want interleave some columns its not possible. (like i want the 1st and 3rd only)

I m still wondering why the nested filter or filter + index didnt work, because it makes sense to me the logic.

It makes sense to you my question?

André

 

[Andre Pereira]

found it.

=CHOOSECOLS(FILTER($G$10:$J$25;$H$10:$H$25=P6);{1;3})

With choosecols i can literally pick the columns that i want from the array produced by the FILTER function

 

[Fluff]

With Choosecols you don't need to use the { }
Your original formula should have been

=FILTER(FILTER($G$10:$J$25;$H$10:$H$25=P6);{1\0\0\1})

or possibly / rather than \

 

[Andre Pereira]

With Choosecols you don't need to use the { }
Your original formula should have been

=FILTER(FILTER($G$10:$J$25;$H$10:$H$25=P6);{1\0\0\1})

or possibly / rather than \

indeed you are right, i feel like an idiot now

Regarding to this, no it return an error

or possibly / rather than \

With Choosecols you don't need to use the { }

Once again you right, i dont need those brakets when using choosecols.

In the end, i found choosecols + filter more easy to use.

thanks for your replies guys.

 

[Fluff]

Glad we could help & thanks for the feedback.