Amit, please go to Names Manager, and give the name of the range as Data,
and use the formula that I provided below. To go to Name Manager using a
short-cut key, hit CTRL+F3
Post back if you need more help.
Regards,
Sam Mathai Chacko (GL)
On Mon, Oct 3, 2011 at 12:10 AM, Amit Desai (MERU)
<amit.de...@merucabs.com>wrote:
> Dear Sam,****
>
> ** **
>
> Thanks for the detailed explanation.... but I could not understand the
> range “Data” – how to create a range. I have selected data set in for
> Vehicle & EntryDate & created data range for the same. But could not move
> further since I could not create “Data” range.****
>
> ** **
>
> And yes, thanks a lot for appreciating Meru’s service....****
>
> ** **
>
> Regards,****
>
> *Amit Desai*
>
> * *
>
> * *
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Sam Mathai Chacko
> *Sent:* 02 October 2011 21:28
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Help needed to find total & Unique count
> from a range of moving dates****
>
> ** **
>
> 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****
>
> ------------------------------
> 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
