Nested Filter Function - it doesnt return the right columns

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
21
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.

1698189995772.png


Thanks in advance for your time

AndreP
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:

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

Book1
GHIJKLM
9SampleColorSizeValueSampleValue
1096Green22846.615101.98
1189Yellow26744.6646619.97
1215Blue36101.9844695.32
1321Orange371197.71231338.41
1450Green27851.48
1542Yellow27408.09
1646Blue20619.97
1742Orange24601.52
1849Green251263.62
1985Yellow33665.92
2044Blue27695.32
2160Orange24487.7
2270Green30215.58
2367Yellow30353.14
2423Blue301338.41
2571Orange281082.57
Sheet3
Cell Formulas
RangeFormula
L10:L13L10=TAKE(FILTER(G10:J25,H10:H25="Blue"),,1)
M10:M13M10=TAKE(FILTER(G10:J25,H10:H25="Blue"),,-1)
Dynamic array formulas.
 
Upvote 0
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:

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

Book1
GHIJKLM
9SampleColorSizeValueSampleValue
1096Green22846.615101.98
1189Yellow26744.6646619.97
1215Blue36101.9844695.32
1321Orange371197.71231338.41
1450Green27851.48
1542Yellow27408.09
1646Blue20619.97
1742Orange24601.52
1849Green251263.62
1985Yellow33665.92
2044Blue27695.32
2160Orange24487.7
2270Green30215.58
2367Yellow30353.14
2423Blue301338.41
2571Orange281082.57
Sheet3
Cell Formulas
RangeFormula
L10:L13L10=TAKE(FILTER(G10:J25,H10:H25="Blue"),,1)
M10:M13M10=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é
 
Upvote 0
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


1698227767911.png
 
Upvote 0
Solution
With Choosecols you don't need to use the { }
Your original formula should have been
Excel Formula:
=FILTER(FILTER($G$10:$J$25;$H$10:$H$25=P6);{1\0\0\1})
or possibly / rather than \
 
Upvote 0
With Choosecols you don't need to use the { }
Your original formula should have been
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top