Excel Advanced Filter

We offer a recycling service and all our customers receive their pick up dates by email once they sign up for a contract.
The data is saved in a worksheet ("RecyclingTax").

I have been asked to display the current week's pickup in a separate worksheet. Obviously, this is a task for the advanced filter.

I do have difficulties making it work. Maybe it is because of the way I put in the criterias?
What is the correct procedure to make it work?

screengrab.PNG
The only reference to a customer in the "RecyclingTaxi" sheet is the customer ID.
I would have to use a VLOOKUP or similar to get the customer's address as well.  
Is it possible to put the filtered data into a table/listobject and then make use of the VLOOKUP?

screengrab2.PNG
Thanks for helping me make the advanced filter work.
sample.xlsm
Massimo ScolaAsked:
Who is Participating?
 
ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
Hi Massimo,

First lets create some NamedRanges;
In RecylingTaxi Sheet:
IDsCol:
=RecyclingTaxi!$C$2:INDEX(RecyclingTaxi!$C:$C,MATCH(99^99,RecyclingTaxi!$A:$A,1))

Open in new window

DatesCol:
=RecyclingTaxi!$B$2:INDEX(RecyclingTaxi!$B:$B,MATCH(99^99,RecyclingTaxi!$A:$A,1))

Open in new window

MemoCol:
=RecyclingTaxi!$E$2:INDEX(RecyclingTaxi!$E:$E,MATCH(99^99,RecyclingTaxi!$A:$A,1))

Open in new window

In Customer's Sheet:
CustomerIDs:
=Customers!$A$2:INDEX(Customers!$A:$A,MATCH(99^99,Customers!$A:$A,1))

Open in new window

NamesCol:
=Customers!$B$2:INDEX(Customers!$B:$B,MATCH(99^99,Customers!$A:$A,1))

Open in new window

StreetCol:
=Customers!$C$2:INDEX(Customers!$C:$C,MATCH(99^99,Customers!$A:$A,1))

Open in new window

StreeNumCol:
=Customers!$D$2:INDEX(Customers!$D:$D,MATCH(99^99,Customers!$A:$A,1))

Open in new window

Final Formulation in Pickups Sheet:
To Check how many records available between two given dates:
=SUMPRODUCT((DatesCol>=$D$3)*(DatesCol<=$D$4))

Open in new window

List of CustomerID, enter below Array Formula confirmed with Ctrl+Shift+Enter in D9 and drag down until you need:
=IFERROR(INDEX(IDsCol,SMALL(((DatesCol)<=$D$4)*((DatesCol)>=$D$3)*ROW(IDsCol),SUM(N(((DatesCol)<=$D$4)*((DatesCol)>=$D$3)=0))+ROWS($1:1))),"")

Open in new window

For Customer Name, enter below formula in B9 and drag down until you need:
=IFERROR(INDEX(NamesCol,MATCH($A9,CustomerIDs,0)),"")

Open in new window

For Street, enter below formula in C9 and drag down until you need:
=IFERROR(INDEX(StreetCol,MATCH($A9,CustomerIDs,0)),"")

Open in new window

For Street Number, enter below formula in D9 and drag down until you need:
=IFERROR(INDEX(StreetNumCol,MATCH($A9,CustomerIDs,0)),"")

Open in new window

For Memo, enter below formula in E9 and drag down until you need:
=IFERROR(INDEX(MemoCol,MATCH($A9,CustomerIDs,0)),"")

Open in new window

See screenshot below as final output:
Final OutputMassimo_MultipleListBetweenTwoDates.xlsm
1
 
Massimo ScolaAuthor Commented:
Apologies for the late reply.

It works great.

Thanks a lot for your help and effort!

Massimo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.