Identifying New Clients

[UncleFoster]

Hello Forum,

I am trying to create a function (vlookup?) that identifies which clients are new in the current month by comparing the client list from the previous month.

I attached the file. you'll see the main table for January right at the top. Its a lot of information, but all I need is to identify new clients using a function.

Can you help out? Thank you for reading

-UncleFoster

[6StringJazzer]

This isn't difficult to do for the specific case of January. You can see that "Dec 2013" is hard-coded into the formula, so you would have to update that if you copy this to a new month. If you want a more general solution that you can copy to a new sheet without updating, then you are going to need a macro solution.

Use this formula in Q4 then copy down:

Please Login or Register  to view this content.

You could also do it with VLOOKUP as you mentioned; I just decided to use MATCH instead.

[UncleFoster]

Thank you! I will try to get this to work for the rest of the months as well.

[etaf]

try this

in Q1 make sure you have this on each sheet
= TEXT(DATE(YEAR(DATEVALUE(MID((RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))),1,3)&" "&MID((RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))),5,4))),MONTH(DATEVALUE(MID((RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))),1,3)&" "&MID((RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))),5,4)))-1,DAY(1)),"mmm yyyy")

assuming always formatted as MMM YYYY
I have taken the sheet name
converted that to a DATE format
taken 1 month away
and turned back to a TEXT

so on each sheet in Q1 you should now have in text the previous month - which is the name of the previous months sheet
so on

sheet named Jan 2014 you get Dec 2013 in cell Q1 - which we can now use to lookup that sheet
sheet named Feb 2014 you get Jan 2014 in cell Q1 - which we can now use to lookup that sheet
sheet named Aug 2013 you get Jul 2014 in cell Q1 - which we can now use to lookup that sheet


now we can use an indirect function to bring that sheet name into a vlookup or index/match to find the new clients

so in Q4
you put
=VLOOKUP(P4,INDIRECT("'"&$Q$1&"'!$B:$B"),1,FALSE)
and copy down

now that can be part of your template for every sheet you create in future months


we can also take the formula further

and use an IF and ISERROR to put if new or existing client as follows
=IF(ISERROR(VLOOKUP(P4,INDIRECT("'"&$Q$1&"'!$B:$B"),1,FALSE)),"New Client","Existing Client")

see attached - just modified for JAN
if you copy the sheet to FEB 2004
then it should look at JAN 2004

[sathishkm]

Thanks for good forumula

[etaf]

your welcome , if you plan to use - you could also - create the list of clients automatically - or have the test on the same row as the client already listed

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

[6StringJazzer]

etaf's solution is very clever and can provide a copy-able version without having to use macros. I do have a suggestion for how to make that formula in Q1 simpler. It depends on the fact that the dates listed starting in A2 will always be in the current month.

Please Login or Register  to view this content.

[etaf]

Wow - 6StringJazzer so much simplier - i got so caught into the sheet names and changing those - missed the obvious
thanks , will keep an eye on that in the future