Filter and List Data From Another Sheet

[Ange Kappas]

Hi All,
Here is the problem. I've got a sheet1 of data which
contains for example the following:


2,50 Bolts ts1 3,10 Green x
2,65 Screws ts3 4,50 Red x
1,30 Hammers se5 5,60 Yellow b
2,50 Nails ts7 2,70 Black c
1,10 Wood gh8 3,75 Green s
2,30 Glue vb0 6,40 Green b
1,90 Bolts sf4 4,50 Pink c
2,50 Bolts sf6 3,00 Red a
2,50 Bolts ts3 2,90 Green x
4,60 Bolts ts3 3,40 Green a

e.t.c.

Now I have a drop down list box on Sheet2 for each column category with the
relevant values and I want to create even on a different sheet all the
results filtered out.
For example if I was to select from my drop down list box on another sheet
lets say Sheet2 just Bolts and Green it would return a list on the other
sheet2 as follows:

2,50 Bolts ts1 3,10 Green x
1,90 Bolts sf4 4,50 Pink c
2,50 Bolts sf6 3,00 Red a
2,50 Bolts ts3 2,90 Green x
4,60 Bolts ts3 3,40 Green a

and further down on the sheet

2,50 Bolts ts1 3,10 Green x
1,10 Wood gh8 3,75 Green s
2,30 Glue vb0 6,40 Green b
2,50 Bolts ts3 2,90 Green x
4,60 Bolts ts3 3,40 Green a

Thanks
Ange

 

[Roger Govier]

Hi Ange

You can achieve what you want with Advanced Filter, but not in the way
you have set out your results.
If you choose Bolts, then you would see rows 1,7,8,9,10 of your database
If you are choosing Bolts AND Green, then you would see just that rows
1,9,10 of your database.
If you choose Bolts OR Green, you will see rows 1,5,6,7,8,9,10

The data will appear as a continuous block however, not be separated out
in two blocks on your result sheet as you have shown.

You need to be sure to start your Advanced Filter from the results
sheet, not from the sheet with your Database.
For help on setting up and using Advanced filter take a look at the
information on Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html
and
http://www.contextures.com/xladvfilter01.html#ExtractWs

 

[Max]

Here's an option using non-array formulas
which delivers results very close to what you're after ..

A sample construct is available at:
http://www.savefile.com/files/6115480
Filter and List Data From Another Sheet.xls

Assume source data in sheet: X, from row2 down

In a new sheet: Y,

In A1 is a DV to select: Bolts, Wood, Glue

In A2:
=IF(ISERROR(SMALL($G:$G,ROW(A1))),"",
INDEX(X!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0)))
Copy A2 to F2

In G2: =IF(OR(X!B2="",$A$1=""),"",IF(X!B2=$A$1,ROW(),""))

Select A2:G2, fill down to say G15
to cover the max expected range of data returned

Cols A to F will return the required filtered rows
for the selection made in A1, all neatly bunched at the top

Similarly in cols H to N ..
(It's easier to set up the 2nd table side-by-side with the 1st one)

In H1 is a DV to select: Green, Pink, Red

In H2:
=IF(ISERROR(SMALL($N:$N,ROW(A1))),"",
INDEX(X!A:A,MATCH(SMALL($N:$N,ROW(A1)),$N:$N,0)))
Copy H2 to M2

In N2: =IF(OR(X!E2="",$H$1=""),"",IF(X!E2=$H$1,ROW(),""))

Select H2:N2, fill down to say N15
to cover the max expected range of data returned

Cols H to M will return the required filtered rows
for the selection made in H1, all rows neatly bunched at the top


---

 

[Ange Kappas]

Hey Max,
Thanks that is great, just one small query, what if I
wanted another criteria selection.
For Example I wanted Bolts & 2,50 to filter the selection further.

Thanks
Ange

 

[Max]

For Example I wanted Bolts & 2,50 to filter the selection further.
Just a minor tweak to the criteria formulas in cols G and N should suffice

See revised sample at:
http://www.savefile.com/files/2144256
Filter and List Data From Another Sheet_v2.xls

In new sheet: Z
(Z is a copy of the previous sheet: Y)

Assume the "2,50" further criteria will be input in B1 (for table 1)
& in I1 (for table 2)

Change the criteria formulas in G2 and N2 to:

In G2:
=IF(OR(X!B2="",$A$1="",$B$1=""),"",IF(AND(X!A2=$B$1,X!B2=$A$1),ROW(),""))

In N2:
=IF(OR(X!E2="",$H$1="",$I$1=""),"",IF(AND(X!A2=$I$1,X!E2=$H$1),ROW(),""))

Then copy down G2, N2 to row15 as before
(No change to the other formulas in cols A to F, H to M)

The above will return the required results

 

[Max]

Oops, missed out including the X!A2="" bit in the error traps

Lines

In G2:
=IF(OR(X!B2="",$A$1="",$B$1=""),"",IF(AND(X!A2=$B$1,X!B2=$A$1),ROW(),""))

In N2:
=IF(OR(X!E2="",$H$1="",$I$1=""),"",IF(AND(X!A2=$I$1,X!E2=$H$1),ROW(),""))

should read as:

In G2:
=IF(OR(X!A2="",X!B2="",$A$1="",$B$1=""),"",IF(AND(X!A2=$B$1,X!B2=$A$1),ROW()
,""))

In N2:
=IF(OR(X!A2="",X!E2="",$H$1="",$I$1=""),"",IF(AND(X!A2=$I$1,X!E2=$H$1),ROW()
,""))

(Revised sample file corrected)

---

 

[Roger Govier]

Hi Max

Very nice solution!!

 

[Max]

Thanks, Roger !