A clientele formula.

  • Thread starter Thread starter Jman
  • Start date Start date
J

Jman

1st. I want to be able to know what day a client comes to see me the most and
2nd.. i want that client to automatically be trasfered to a different sheet
under "mon, tues, wed, fri, sat, column, depending what's the most frequent
day the client comes in. If the client come a total of 4 times ,,, 2 days
wed, 2 days thur. That clinent does not need to be transfered over.. only
when he/she comes in another wed, then the client can be trasfered under Wed
column.

Here is my set up .

(On sheet 1)

Clients Names
Column A1 to A 2000



Client Dates
Column B1 to B 2000
8/22/2008
---------------------------------------------------------------------------

(On sheet 2)

Column Names (A to F ) I have ( mon, tue, wed, thurs, fri, sat.)

This is where i need the clients to be automatically transfered accordingly
to the most frequent day they came in.


-------------------------------------------------------------------------------

Here is an example (all these days happen to be tuesday) therefore since
this client prefers only to come on tuesday he should be automatically
transfered to sheet 2 under the "tuesday" column. And so on for every
client that under that column

John Smith 8-22-08
John Smith 8-1-08
John Smith 8-29-08
 
Here's a model that might appeal to you ..

Illustrated in this sample:
http://freefilehosting.net/download/3mm1d
Group Client by Preferred Visit Day.xls

In Sheet2,
In A2:
=IF(Sheet1!A1="","",IF(COUNTIF(Sheet1!A$1:A1,Sheet1!A1)>1,"",ROWS($1:1)))

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, eg down to
row100? This extracts dynamically the unique list of clients into col B

Label in C1:H1 : Mon, Tue, Wed, Thu, Fri, Sat
In C2
=IF($B2="","",SUMPRODUCT((Sheet1!$A$1:$A$100=$B2)*(TEXT(Sheet1!$B$1:$B$100,"ddd")=C$1)*(Sheet1!$B$1:$B$100<>"")))
Copy C2 across to H2, fill down. Adapt the ranges to suit the max expected
extent of source data

Label in J1:O1 : Mon, Tue, Wed, Thu, Fri, Sat
In J2
=IF(SUMPRODUCT(--($C2:$H2=""))=6,"",IF(COUNTIF($C2:$H2,MAX($C2:$H2))>1,"",IF(INDEX($C$1:$H$1,MATCH(MAX($C2:$H2),$C2:$H2,0))=J$1,ROW(),"")))
Copy J2 across to O2, fill down

Label in Q1:V1 : Mon, Tue, Wed, Thu, Fri, Sat
In Q2:
=IF(ROWS($1:1)>COUNT(J:J),"",INDEX($B:$B,SMALL(J:J,ROWS($1:1))))
Copy Q2 across to V2, fill down
Cols Q to V returns the desired results (Hide away cols A to P)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
 
Max incase you see this again..
In your file,
How did you get the quick "scroll bar" to hide your formula cells?
 
How did you get the quick "scroll bar" to hide your formula cells?
It's under Group & Outline.
Just select the cols (or rows) to group,
then click Data > Group & Outline > Group
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
 
Back
Top