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,Sheet 1!A1)1,"",ROWS($1:1)))

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(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
---
"Jman" wrote:
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

Thanks for the hard work.. It's exactly what i needed.
The file attached is perfect also.

"Max" wrote:

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,Sheet 1!A1)1,"",ROWS($1:1)))

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(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
---
"Jman" wrote:
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

Welcome, Jman
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Jman" wrote in message
...
Thanks for the hard work.. It's exactly what i needed.
The file attached is perfect also.

Max incase you see this again..
In your file,
How did you get the quick "scroll bar" to hide your formula cells?

"Max" wrote:

Welcome, Jman
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Jman" wrote in message
...
Thanks for the hard work.. It's exactly what i needed.
The file attached is perfect also.

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

Thanx again.

"Max" wrote:

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

No prob, cheers.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Jman" wrote in message
...
Thanx again.