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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Aladin Akyurek

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

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,192

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,192
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,132
Messages
5,509,339
Members
408,727
Latest member
Cantello

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top