Lapsed Account Rev

[Rinki]

Hi,


With the help of the forum member here,I could develop a column that would display today - 13th month but how to show those lapsed account names which would say that these are the accounts who have not given us any rev at all in last months from this month onward.
Corresponding to it, I am unable to plot its corresponding rev in other years where they gave us some or the other rev
also please see it needs to be refreshed every month.
I have attached dummy file.

 

[Hui]

Have a look at the attached

I have changed columns A and B to dates rather than text, it hugely simplifies the formulas

I have added data in Row 72 so that the second list has at least one value

 

[Rinki]

Hi Hui,

1) Appreciate your help here but I dont have the freedom to change it to dates.

2) Nextly your results for Not advertised at all till date doesnt give me unique Account Names.

3) The results are not correctly getting displayed with the help of your formula bcz as compared to my results which I did manually.

Please suggest alternative without changing to dates- accounts who gave us zero rev in last 13 months vs their corresponding rev in 2013 & 2014

Thx

 

[Hui]

I'd then use the same approach adding hidden helper columns to convert the Text dates to real Dates

If that isn't acceptables

After that I'd go to using an Advanced Filter with helper Columns as the formulas to get a Formulaic answer are getting too complex

 

[Rinki]

Hi Hui,

I have simplified the issue.

Please see the file attached where I have derived what I wanted to but with a difficult pivot.'

May be it ll give you an idea of what I am looking for.

 

[Hui]

It's not very clear from that file what your after ?

Nothing highlighted no vba code?

 

[Rinki]

I dont know vba I need output as what i have shown in the file.