Hi Amit,
Follow steps carefully in sequence
Add 3 named ranges as below
Vehicle=LOG!$E$2:INDEX(LOG!$E$2:$E$10000,MATCH(REPT("z",20),LOG!$E$2:$E$10000,1))
EntryDate=LOG!$B$2:INDEX(LOG!$B$2:$B$10000,MATCH(9E+305,LOG!$B$2:$B$10000,1))
Data=IF((EntryDate>=Sheet2!$A1)*(EntryDate<=Sheet2!$B1)=1,Vehicle,"")
Once you are done with that, use the following formulas in your Revisit
summary sheet
D5=SUMPRODUCT((EntryDate>=$A5)*(EntryDate<=$B5))
E5=SUM(IF(FREQUENCY(IF(LEN(Data)>0,MATCH(Data,Data,0),""),
IF(LEN(Data)>0,MATCH(Data,Data,0),""))>0,1)) *Array Formula*
F5=D5-E5
G5=SUM(IF(FREQUENCY(IF(LEN(Data)>0,MATCH(Data,Data,0),""),
IF(LEN(Data)>0,MATCH(Data,Data,0),""))=G$3,1)) *Array Formula*
H5:L5 - Copy formula from G5
You can continue from M5 to a few more columns to the right, (maybe till
frequency of 10) and copy the above formula
OR
have all frequencies greater than or equal to 7 by slightly modifying the
above formula as this
M5=SUM(IF(FREQUENCY(IF(LEN(Data)>0,MATCH(Data,Data,0),""),
IF(LEN(Data)>0,MATCH(Data,Data,0),""))*>*=M$3,1)) *Array Formula*
Note the last part where I added a greater than symbol. Unfortunately, I am
not able to upload the file now.
Happy Meru Cabbing. We enjoy your service.
Regards,
Sam Mathai Chacko (GL)
On Sun, Oct 2, 2011 at 6:18 PM, Amit Desai (MERU)
<amit.de...@merucabs.com>wrote:
> Dear All,****
>
> ** **
>
> Need your help..****
>
> ** **
>
> Please find attached excel file. I need to analyse revisit of vehicles in
> workshop with a moving average of 30 days.... 1st aug to 1st Sept, 2nsAug
> to 2nsSept. Thus I need total count of vehicles in a period & unique count
> of vehicles in a period. ****
>
> ** **
>
> One sheet is log – raw data & other sheet is Revisit summary where in I
> need answer is orange cells. I could get the result fir the first period (1
> st Aug to 1st Sept)butcould not get the correct answer for second period
> onwards....****
>
> ** **
>
> Also suggest if any changes can be done to simplify the formula used in
> current file...****
>
> ** **
>
> Regards,****
>
> *Amit Desai*****
>
> ------------------------------
> Disclaimer: This message and its attachments contain confidential
> information and may also contain legally privileged information. This
> message is intended solely for the named addressee. If you are not the
> addressee indicated in this message (or authorized to receive for
> addressee), you may not copy or deliver any part of this message or its
> attachments to anyone or use any part of this message or its attachments.
> Rather, you should permanently delete this message and its attachments (and
> all copies) from your system and kindly notify the sender by reply e-mail.
> Any content of this message and its attachments that does not relate to the
> official business of Meru Cab Company Pvt. Ltd. must be taken not to have
> been sent or endorsed by any of them. Email communications are not private
> and no warranty is made that e-mail communications are timely, secure or
> free from computer virus or other defect.
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
