Avatar of Massimo Scola
Massimo Scola
Flag for Switzerland asked on

Need advise with worksheet design/advanced filter

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
Microsoft Excel

Avatar of undefined
Last Comment
Massimo Scola

8/22/2022 - Mon
Saqib Husain

Enter this formula in I2 and copy it down

=IF(C2=C3,H3,H2)
Massimo Scola

ASKER
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
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Massimo Scola

ASKER
sorry for my late reply
thanks a lot. the array formula works very well
massimo
Your help has saved me hundreds of hours of internet surfing.
fblack61