This is raw data 2 years of one salesman
STEP 1
Arrange this into order JAN FEB and so on , since I took pivot results i don't know how to order
STEP 2
List how many new clients on sheet 2 for JAN
STEP 3
List how many new in FEB and list their accounts on boxes 1 2 3 4 and so on depending how many
STEP 4
See whats new on March by comparing JAN FEB and so on
The first month JAN 2013 definitely wont have new clients ,
thats the start point , so it should only count the clients that month
Re: How many new clients monthly and list them
Here is what I have managed..
there is one limitation in your desired output as there are only 256 columns in the excel you can't go beyond that limit ( I guess )
Check the attached file..
03 11 how many new seller monthly.xlsx
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Yes Vikas you smelled correct
I realised I had pasted the wrong file , that was totals
of 10 heads , here is the corrected file of 1 saleman
Re: How many new clients monthly and list them
Now this is much more fast and efficient and Flexible as well..
No need to revise formula for New Data..
For NEW
Formula:
Please Login or Register to view this content.
For ACC Arrayed formula ( drag horizontally and vertically...)
=IF(COLUMNS($A$1:A1)<=$B2,INDEX(Sheet1!$A$1:$A$6000,SMALL(IF(INDEX(INDIRECT("Sheet1!$B$2:$W$"&COUNTA(Sheet1!$A:$A)),,MATCH(TEXT($A2,"mmm yyyy"),Sheet1!$B$1:$W$1))<>0,ROW(INDIRECT("$A$2:$A$"&COUNTA(Sheet1!$A:$A)))),COLUMNS($A$1:A1))),"")
Check the attached file..
03 11 how many new seller monthly.xlsx
Running two pivots is time consuming
Nice try Vikas
If anyone is planning for a macro ,
you will need to consider re organising
the data into jan feb and so on
Last edited by makinmomb; 11-04-2014 at 01:36 AM.
Sorry I uploaded the wrong file ..
But formulas given are correct..
Give me a second to prepare a new one..
I will be back..
Re: How many new clients monthly and list them
Here is the correct file ..
Fast and flexible..
have a look..
This is not correct , look B089 is not a new client for FEBRUARY
His there already in JANUARY , that is one straight see on the first
two rows , thus I am not looking into other rows
MONTH NEW 1
Jan-13 29 B089
Feb-13 21 B089
Thank Vikas, i do not understand
Hi, Refer the attachment...
☚ Click ★ just below left if it helps, Boo?ath?
Hi, your profile says you are using the ver 2010, if you are using lower than 2010, you can adapt the vikas's formula, with helper column in A
boopathiraja formula sounds to do the work
But data was needed to be JAN FEB and so on
I will still wait for a macro and collect your formula
Ok, thats fine , you can manually sort column A, results will be based on Column A only, Since you are looking for macro, someone will come up with VBA solution
Yeah I have requested a column arrangement macro separately
ok, Ref the attachment now, a small formula put in Column A, and corrected month which stored as text,
This beautiful formula actually closes the thread I think
At times you can have a solution but no presentation of data
Find attached the neatness of this data
I used pivots but used some other long methods to
set the data as presented
Re: How many new clients monthly and list them
Hi Makin,
Here is the solution of your first query..
Formula:Please Login or Register to view this content.
Flexi and Flexi2 are FLEXIBLE NAMED RANGES..
I may have reinvented it, but I guess I have just tasted the power of excel by doing so.
It was a great experience.. as It cut of the Main formula length and made it more understandable..
Flexi (on row 1)
Formula:Please Login or Register to view this content.
Flexi2 ( on row 2)
Formula:Please Login or Register to view this content.
Check the attachment...
Re: How many new clients monthly and list them
Here is the attached file containing both the solutions..
But before pasting data to this sheet..
You have to run the code below to make data in ascending order by Month and Year.. thats important
Then you can copy the output by this code and paste it in the file I am attaching..
Delete the grand totals for both rows and columns..
Note:- Don't run this code in this main file as this will disturb the flexi reference.. do this preliminary activity in a separate file..
AND one more thing, the columns may range up to W, be careful while pasting new data regarding the no. of columns it takes..
Here is the code..
Check the attached file..
Please Login or Register to view this content.
This work is beyond clean , hats Vikas
Re: How many new clients monthly and list them
Here is your complete package..
For New ACC Client list ( Arrayed Formula , Drag drop horizontally and vertically..)
Check the attached file..Formula:Please Login or Register to view this content.
03 11 how many new seller monthly (1).xls
Last edited by Vikas_Gautam; 11-05-2014 at 12:24 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks