Latest text entries

[jonhall]

Hi guys,

I was hoping someone might be able to help me with the following problem.

I have an excel spreadsheet that tracks my appointments and shows various things such as revenue generated, tax due, clients gained in a time frame, etc.

I'd like it to be able to show clients lost in a time frame automatically, without having to manually input when a session is their last.

I have a column (A) which shows the date of each session. Column B shows the name of the client. I thought the solution might work as follows (but can't figure out how to do these steps):

# A hidden column (C) that would return a value showing when the latest session occurs, ie: the most recent date in A for each name repeated in B.
# A another column (D) that would return a value if this latest session was more than 3 weeks ago, ie: current date - session date (A) > 21 days and value in C.

I can then do the rest from there, I think.

This way, if a client restarts after a break it will automatically drop them from being an ex-client.

Hope I've explained this sufficiently well.

Any help would be greatly appreciated.

 

[Aladin Akyurek]

Date Client Latest session? Last session? Last session > 3 weeks?
01/03 Mr X 01/07
01/04 Mr X 01/07
01/05 Mr X 01/07
01/06 Mr X 01/07
01/07 Mr X 01/07 Y Y

Hope that makes it clear.

Thanks,

Jon

Are just those two Y's the desired results?

 

[jonhall]

It doesn't necessarily need to be Ys, but something to show that that session was the last one. So, in answer to your question, yes.

The >21 days bit I already have in there, just need to pick out the last session as opposed to last session date next to all sessions.

Thanks,

Jon