|
Transfer Per catagory
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. |
Transfer Per catagory
|
Transfer Per catagory
Thanks for the advice. "Herbert Seidenberg" wrote: Use Excel 2007 and PivotTable. Not easy: http://www.savefile.com/files/1811928 |
Transfer Per catagory
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. |
Transfer Per catagory
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 --- |
Transfer Per catagory
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. |
Transfer Per catagory
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. |
Transfer Per catagory
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 --- |
Transfer Per catagory
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 --- |
Transfer Per catagory
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.. |
| All times are GMT +1. The time now is 09:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com