Latest text entries

jonhall

New Member
Joined
Jul 27, 2010
Messages
12
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
=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

New Member
Joined
Jul 27, 2010
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Jul 27, 2010
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Jul 27, 2010
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Jul 27, 2010
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,715
Messages
5,660,438
Members
418,580
Latest member
JP82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top