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]

Try...

C2, control+shift+enter, not lust enter, and copy down:

=MAX(IF($B$2:$B$100=B2,$A$2:$A$100))

D2, just enter and copy down:

=IF(TODAY()-C2 > 21, "#", "")

 

[lenze]

Hi and welcome to the boardd!!
You can create a Pivot Table with Names in the Row Field and Date in the Data Field.
Set the Data Field to "Max" to display the latest date. You will also have to format it as Date to display. Now use Conditional Formatting on you Date Field, Formula Is

=Today() > A2+21

<TODAY()-21[ p code]<>Select a color to highlight those dates
See www.contextures.com for good discussions and tutorials on both PTs and CF

HTH
lenze

 

[jonhall]

Hi guys,

Thanks for your help.

Aladin - your suggestion nearly works. The only slight problem is the C column returns the date of the last session next to each and every session, ie: if someone had a session on 01/02, 01/03, 01/04 and 01/05 it returns 01/05 next to each date. Is there anyway it can just return the last date once? Thanks.

lenze - I don't know much / anything about Pivot Tables so will see if this other method will work. Thanks very much for your help though.

 

[Aladin Akyurek]

Hi guys,

Thanks for your help.

Aladin - your suggestion nearly works. The only slight problem is the C column returns the date of the last session next to each and every session, ie: if someone had a session on 01/02, 01/03, 01/04 and 01/05 it returns 01/05 next to each date. Is there anyway it can just return the last date once?
...

Sure. Would you post a small sample along with the desired results?

 

[jonhall]

Sure. Would you post a small sample along with the desired results?

Hi Aladin,

I've pasted the bit below. Hope it comes out clearly.

Date Client Latest session? Latest session > 3 weeks
26/05/2010 Suzy Deakin 26/07/2010
26/05/2010 Kate Rijnen 26/07/2010
26/05/2010 Jaye Ibbotson 04/08/2010
26/05/2010 Andy Phillips 07/07/2010 1
28/05/2010 Joan-Marie Williams28/05/2010 1
28/05/2010 Jaye Ibbotson 04/08/2010
02/06/2010 Jaye Ibbotson 04/08/2010
02/06/2010 Charles Johnson 03/08/2010
02/06/2010 Steve Chapman 29/06/2010 1
02/06/2010 Frank Reddiough 19/07/2010
02/06/2010 Pete Woolley 03/08/2010
03/06/2010 Vivien McAlpine 02/08/2010
03/06/2010 Erwin Igharas 03/08/2010

As you can see for, say, Jaye Ibbotson, it shows the latest date (tomorrow) next to all of the sessions listed. Therefore when I try and show how many clients I have lost (haven't had a session in more then 3 weeks) for a certain date range it actually shows the total number of sessions those clients had (ie: if three clients had 10 sessions each, it shows 30 as the lost number of clients).

Therefore it would be good if it could show the date of last session only against that last / latest session. Or if it could do this is some other column / table.

Thanks,

Jon

 

[Aladin Akyurek]

D2, just enter and copy down:

=(TODAY()-C2>21)+0

Legend: 1 = 3 weeks or more past since last session, 0 = period since the last session is still within 3 weeks.

 

[jonhall]

Hi Aladin,

I already have a similar formula - =IF(TODAY()-C2>21,1,"") - which returns a 1 for more then 3 weeks and nothing for less. This is in column D and is what is returning the 1s next to Andy Phillps, Joan-Marie Williams and Steve Chapman on the table I copied and pasted. It's not very clear, sorry about that.

I could do with something that shows just the very last session date that person had, not the last session date next to all the session dates they had, if you see what I mean. Sorry if this isn't very clear.

Thanks again for your help,

Jon

 

[Aladin Akyurek]

Hi Aladin,

I already have a similar formula - =IF(TODAY()-C2>21,1,"") - which returns a 1 for more then 3 weeks and nothing for less. This is in column D and is what is returning the 1s next to Andy Phillps, Joan-Marie Williams and Steve Chapman on the table I copied and pasted. It's not very clear, sorry about that.

I could do with something that shows just the very last session date that person had, not the last session date next to all the session dates they had, if you see what I mean. Sorry if this isn't very clear.

Thanks again for your help,

Jon

Is it possible for you to post the desired results?

 

[jonhall]

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