Avatar of Massimo Scola
Massimo ScolaFlag 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:

User generated image
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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Enter this formula in I2 and copy it down

=IF(C2=C3,H3,H2)
Avatar of Massimo Scola
Massimo Scola
Flag of Switzerland image

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.

User generated imageUser generated image
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Massimo Scola
Massimo Scola
Flag of Switzerland image

ASKER

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo