Need advise with worksheet design/advanced filter

Massimo Scola
Massimo Scola used Ask the Experts™
on
We distribute a local magazine and I store all data associated with it: product name (how many months?), date ordered, start and end of the subscription as well as the days left till the subscription expires.

I have to contact all those customers whose subscription will end soon. In order to make this easy for me, I created a worksheet and used an advanced filter to copy all those customers whose subscription will end in 30 days or less. Some customers may already have bought a second subscription and thus don't need to show up on the filtered data. Please have a look at an example:

screenshot
In this example, Simone has two subscriptions: One will end in less than 30 days and the other will expire in 155 days. (=she renewed her subscription). With the advanced filter, Simone's first subscription would show up. What do you suggest I do so that this first subscription doesn't show up?  

I've attached an example to this post.  It should be possible, shouldn't it? I am open/free to make changes to the worksheet.Thank you for your help and suggestions
subscriptions.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Enter this formula in I2 and copy it down

=IF(C2=C3,H3,H2)
Massimo ScolaSoftware Engineer

Author

Commented:
Thank you for your reply.

It doesn't quite work.. in my example, Flurina has already renewed her subscription, yet she still shows up in the list.

list of customersfiltered list
Sorry I did not realize that there could be more than two lines for a subscriber. Also Some of the subscribers are not on contiguous lines like Doris on line 2 and at the bottom of the list.

This formula will take care of both cases. Remember that this is an ARRAY formula and will have to be entered by pressing Ctrl-Shift-Enter

=MAX(IF(C:C=C2,H:H))
Massimo ScolaSoftware Engineer

Author

Commented:
sorry for my late reply
thanks a lot. the array formula works very well
massimo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial