I have four variables: ID (col HZ), Pick (col IA), Type of Visit (col
IB), and Current_Day (IC). ID is unique code and is repeated for every
visit; Pick takes only the last visit and codes it as 1, else 0; Type
of Visit picks its values from another variable that ranks the visit as
1, 2, 3...i and outputs either "N" or "R"; Curr_Day checks the value of
the date of visit to between 21st of previous month and 20th of current
month (e.g. March 21 to April 20, inclusive) and ouputs 0 or 1. Thus if
I want to pick all visits during the reporting period I simply
autofilter and select Curr_Day=1. I can further select single clients
from "Pick". However, a client can make more than one visit a month. In
my formula, only the first visit will be picked as new, the rest are
revisits.

HZ IA IB IC
3 1 N 0
8 1 N 1
17 0 N 0
17 0 R 0
17 1 R 1
18 0 N 1
18 1 R 1

formulae used
IA "=IF($HZ2-$HZ3=0,0,1)"
IB "=IF($EG2=1,"N",IF($EG2<1,"R",""))" where EG9 rank of visit (not
among the 4 fields)
IC "=IF(AND($DJ8$IR$2,$DJ8<$IR$3),1,0)" where DJ is date of visit and
IR2 start date, IR3 is end date

How can I create a formula that codes all visits as new so long as the
first visit falls within the month of interest? Will this solve my
problem, since at the end of day, I just want to analyse new visits and
revisits and pick clients only once, regardless of the number of visits
within the month. I would really appreciate any assistance on this. I
have reached my limit of creativity.
Cheers.

Could you set up a pivot table and using the top left box, select
whether current period or not, and then put client code number as row
headers, and in the middle use sum of client code number.

I think this will tell you how many times each client has visited in
the current month.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=529174

Thanks. The problem is not to recall the number of times a client has
visited. How can I code each visit as new so long as the first visit is
within the month?