Transfer Per catagory

[Jman]

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.

 

[Herbert Seidenberg]

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

 

[Jman]

Thanks for the advice.

 

[Max]

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,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 (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(C2>0,D2>0,E2>0),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

 

[Max]

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,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 (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(C2>0,D2>0,E2>0),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]

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.

 

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

 

[Max]

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

 

[Jman]

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

 

[Max]

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