How can i automatically transfer clients columns on another sheet..
In sheet 1 i have Clients and the Date they came in
A2: A1000 Client Names and B2: B1000 Client Dates.

In sheet 2 I have Coumn A thru L Jan, Feb Mar,Apr, May, June, July, Aug,
Sept, Oct, Not, Dec.


1st.I want to know which clients came in last 3 months in a row..
2nd If the they can be placed under the appropriate month they came in.
Is this possible?

Thanx in advance.

Ps: Mike or Max, you guys helped me out before in a similar question.. If
you can do this one more time, i'd really appreciate it. But if it can not be
done, please let me know ..
Thanks
Jman.

Use Excel 2007 and PivotTable.
Not easy:
http://www.savefile.com/files/1811928

Thanks for the advice.
"Herbert Seidenberg" wrote:

Use Excel 2007 and PivotTable.
Not easy:
http://www.savefile.com/files/1811928

Here's another play - formulas driven ...

Illustrated in this sample:
http://freefilehosting.net/download/40f35
Group Client by Last 3 Mths in a row Visits.xls

Source data in Sheet1, cols A & B, from row1 down.
Col A = Client names, Col B = real dates

In sheet: By Last 3 Mths in a row Visits,
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 (as
in the previous solution)

Label in C1:H1, the 1st-of-month real dates (formatted as mmm-yy) :
Jan-08, Feb-08, Mar-08, Apr-08, May-08, Jun-08

In C2:
=IF($B2="","",SUMPRODUCT((Sheet1!$A$1:$A$100=$B2)* (TEXT(Sheet1!$B$1:$B$100,"mmmyy")=TEXT(C$1,"mmmyy" ))*(Sheet1!$B$1:$B$100<"")))
Copy C2 across to H2, fill down. Adapt the ranges to suit the max expected
extent of source data. This part counts the client's visits in each mth/yr.

Labels in J1:M1 : Jan08-Mar08, Feb08-Apr08, Mar08-May08, Apr08-Jun08
In J2:
=IF(SUMPRODUCT(--($C2:$H2=""))=6,"",IF(AND(C20,D20,E20),ROW(),"" ))
Copy J2 across to O2, fill down. This part "flags" clients who visited 3
mths-in-a-row. The final results will be retrieved in the adjacent range cols
Q to T

In Q1, copied to T1: =J1 (to reproduce the col labels from left)
In Q2:
=IF(ROWS($1:1)COUNT(J:J),"",INDEX($B:$B,SMALL(J:J ,ROWS($1:1))))
Copy Q2 across to T2, fill down. Cols Q to T returns the desired results
(Hide away cols A to P).

Extend the construct to suit ..

P/s: The previous solution for your other posting in mid Sep08 is in sheet:
By Preferred Visit Day. I've retained it for your easy reference.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Jman" wrote:
How can i automatically transfer clients columns on another sheet..
In sheet 1 i have Clients and the Date they came in
A2: A1000 Client Names and B2: B1000 Client Dates.

In sheet 2 I have Coumn A thru L Jan, Feb Mar,Apr, May, June, July, Aug,
Sept, Oct, Not, Dec.


1st.I want to know which clients came in last 3 months in a row..
2nd If the they can be placed under the appropriate month they came in.
Is this possible?

Thanx in advance.

Ps: Mike or Max, you guys helped me out before in a similar question.. If
you can do this one more time, i'd really appreciate it. But if it can not be
done, please let me know ..
Thanks
Jman.

Wonder if you missed the response earlier
------------------------------------------
Here's another play - formulas driven ...

Illustrated in this sample:
http://freefilehosting.net/download/40f35
Group Client by Last 3 Mths in a row Visits.xls

Source data in Sheet1, cols A & B, from row1 down.
Col A = Client names, Col B = real dates

In sheet: By Last 3 Mths in a row Visits,
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 (as
in the previous solution)

Label in C1:H1, the 1st-of-month real dates (formatted as mmm-yy) :
Jan-08, Feb-08, Mar-08, Apr-08, May-08, Jun-08

In C2:
=IF($B2="","",SUMPRODUCT((Sheet1!$A$1:$A$100=$B2)* (TEXT(Sheet1!$B$1:$B$100,"mmmyy")=TEXT(C$1,"mmmyy" ))*(Sheet1!$B$1:$B$100<"")))
Copy C2 across to H2, fill down. Adapt the ranges to suit the max expected
extent of source data. This part counts the client's visits in each mth/yr.

Labels in J1:M1 : Jan08-Mar08, Feb08-Apr08, Mar08-May08, Apr08-Jun08
In J2:
=IF(SUMPRODUCT(--($C2:$H2=""))=6,"",IF(AND(C20,D20,E20),ROW(),"" ))
Copy J2 across to O2, fill down. This part "flags" clients who visited 3
mths-in-a-row. The final results will be retrieved in the adjacent range cols
Q to T

In Q1, copied to T1: =J1 (to reproduce the col labels from left)
In Q2:
=IF(ROWS($1:1)COUNT(J:J),"",INDEX($B:$B,SMALL(J:J ,ROWS($1:1))))
Copy Q2 across to T2, fill down. Cols Q to T returns the desired results
(Hide away cols A to P).

Extend the construct to suit ..

P/s: The previous solution for your other posting in mid Sep08 is in sheet:
By Preferred Visit Day. I've retained it for your easy reference.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---

yeah i missed the response.

I'l give it a try tomorrow and let you know how it turned out.
Thanks for the extra effort.


"Max" wrote:

Here's another play - formulas driven ...

Illustrated in this sample:
http://freefilehosting.net/download/40f35
Group Client by Last 3 Mths in a row Visits.xls

Source data in Sheet1, cols A & B, from row1 down.
Col A = Client names, Col B = real dates

In sheet: By Last 3 Mths in a row Visits,
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 (as
in the previous solution)

Label in C1:H1, the 1st-of-month real dates (formatted as mmm-yy) :
Jan-08, Feb-08, Mar-08, Apr-08, May-08, Jun-08

In C2:
=IF($B2="","",SUMPRODUCT((Sheet1!$A$1:$A$100=$B2)* (TEXT(Sheet1!$B$1:$B$100,"mmmyy")=TEXT(C$1,"mmmyy" ))*(Sheet1!$B$1:$B$100<"")))
Copy C2 across to H2, fill down. Adapt the ranges to suit the max expected
extent of source data. This part counts the client's visits in each mth/yr.

Labels in J1:M1 : Jan08-Mar08, Feb08-Apr08, Mar08-May08, Apr08-Jun08
In J2:
=IF(SUMPRODUCT(--($C2:$H2=""))=6,"",IF(AND(C20,D20,E20),ROW(),"" ))
Copy J2 across to O2, fill down. This part "flags" clients who visited 3
mths-in-a-row. The final results will be retrieved in the adjacent range cols
Q to T

In Q1, copied to T1: =J1 (to reproduce the col labels from left)
In Q2:
=IF(ROWS($1:1)COUNT(J:J),"",INDEX($B:$B,SMALL(J:J ,ROWS($1:1))))
Copy Q2 across to T2, fill down. Cols Q to T returns the desired results
(Hide away cols A to P).

Extend the construct to suit ..

P/s: The previous solution for your other posting in mid Sep08 is in sheet:
By Preferred Visit Day. I've retained it for your easy reference.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Jman" wrote:
How can i automatically transfer clients columns on another sheet..
In sheet 1 i have Clients and the Date they came in
A2: A1000 Client Names and B2: B1000 Client Dates.

In sheet 2 I have Coumn A thru L Jan, Feb Mar,Apr, May, June, July, Aug,
Sept, Oct, Not, Dec.


1st.I want to know which clients came in last 3 months in a row..
2nd If the they can be placed under the appropriate month they came in.
Is this possible?

Thanx in advance.

Ps: Mike or Max, you guys helped me out before in a similar question.. If
you can do this one more time, i'd really appreciate it. But if it can not be
done, please let me know ..
Thanks
Jman.

Label in C1:H1, the 1st-of-month real dates (formatted as mmm-yy) :
Jan-08, Feb-08, Mar-08, Apr-08, May-08, Jun-08


Is it possible to add the last 6 months.. July thur December to complete
the year?



PS. i did the " transfer per preffered day" formula you gave me and it
worked perfectly i can't find teh thread though. I had some problems with my
account a 4wkago,, it was being updated.. at one point i was a new member and
lost the history of all posts.. A couple days later it came back, but some
threads, and posts were missing. lol.

Is it possible to add the last 6 months..
July thru December to complete the year?

Sure, here's the extended version, ready for your use:
http://freefilehosting.net/download/415j4
Group_Client_by_Last_3_Mths_in_a_row_Visits_v2.xls

PS. i did the " transfer per preferred day" formula you gave me and it
worked perfectly i can't find the thread though.

Here's the direct link to that thread:
http://tinyurl.com/5qc4hd

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

Thanks exactly what i need.. it works great
and i put it to good use..



"Max" wrote:

Is it possible to add the last 6 months..
July thru December to complete the year?

Sure, here's the extended version, ready for your use:
http://freefilehosting.net/download/415j4
Group_Client_by_Last_3_Mths_in_a_row_Visits_v2.xls

PS. i did the " transfer per preferred day" formula you gave me and it
worked perfectly i can't find the thread though.

Here's the direct link to that thread:
http://tinyurl.com/5qc4hd

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

Welcome, Jman
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Jman" wrote in message
...
Thanks exactly what i need.. it works great
and i put it to good use..