Posts by hyperdreamz

    Hi All


    Need a little help in calculating the maximum concurrent connected calls in a half hour based on a dump of start time and end time of 4000000 call records in ACCESS.


    I have been supplied a raw database in ACCESS.


    For your reference I have attached a sample EXCEL file to represent the data I have in ACCESS.



    I need to populate columns “E” to “AZ” with the count.


    For example


    Start Time 05/08/2008 1:57 AM
    Stop Time 05/08/2008 2:37 AM


    Would have a count in the following intervals


    01:30 to 02:00 = 1
    02:00 to 02:30 = 1
    02:30 to 03:00 = 1



    Any help or suggestion would be highly appreciated.


    Hyper Dreamz

    Files

    • TEST.xls

      (16.38 kB, downloaded 121 times, last: )

    Re: Disable "Automatic Calculation" only on CERTAIN SHEETS in a Workbook ?


    .




    Thanks a million


    Appreciate the assistance



    However even with that my sheets going all crazy on me


    I jus give up???


    I?ll probably have to redo the whole thing from scratch cause something is screwing something somewhere an I cant seem to figure it out?????


    Thanks once aging


    Hyperdreamz






    .

    Re: Disable "Automatic Calculation" only on CERTAIN SHEETS in a Workbook ?


    Plz forgive me if I seem like a little excel newbie


    But I am one :)


    I'm not a pro at VB so I'm kind of lost here, my macro creating skills a limited to CTRL+C & CTRL+V and most basic editing


    I have 9 sheets in the workbook


    I have sheets 2 - 6 to be on Automatic Calculation


    And Sheets 7 - 9 on Manual Calculation


    Could u be so kind as to give specific steps as to achieve this result?







    .






    Disable "Automatic Calculation" only on CERTAIN SHEETS



    1) Is it possible to disable "Automatic Calculation" only on CERTAIN SHEETS in an Excel Workbook?


    2) Is it possible to do an ActiveSheet.Calculate every time a user uses a particular Drop-Down Menu?


    I have a mini business application I just got done with


    I use o LOT of SUMPRODUCTS on certain sheets


    And the data that it working with is vast


    So due to the Automatic calculate function being enabled by default my comp just goes loopy on me every 30 second?s I?m using the sheet (switching between sheets)


    So I went ahead and disable Automatic calculations and made it Manual


    Provided a macro do an ActiveSheet.Calculate when I need it to update


    Sadly this has caused more problems than it solved


    On certain sheets I have some VLOOKUP's INDEX's that need to be automatically calculated but since I turned the damm thing off it will only update when I run the macro


    But this screw's up the resultant data on screen producing hundreds of errors (UGLY)


    This has totally frustrated me after putting in so much effort into this project it finally seemed to be done then this......



    I would really appreciate any assistance with this problem



    Regards,
    HyperDreamz




    Re: COUNTIF data after applying Auto filter


    .




    headings are replaced too


    the formatting re-applied



    but if it’s needed I can go the A2 way too


    so long as this results in only the filtered data appearing to the right


    if all the data reappears


    the and I save the file in error


    it will never open (at least on the lame ass mc I work wit)


    jus ran a check wit this week’s data 175MB


    had to delete it because it kept freezing every time I opened it


    I think it’s due to all the SUMPRODUCT's I have working in there


    Hyperdreamz




    .

    Re: COUNTIF data after applying Auto filter


    What happens is


    On the sheet DATA Complete select all clear is done


    Then a paste only values is done


    This is done because next conditional formatting is applied that changes week to week




    I too researched the filter bit ur talking bout



    Plz forgive my naivety


    The best way I can describe it is that the cells that have the calculations in will be cleared every week


    And new data pasted in there


    Resulting in blank cells where ur doing the calculations now


    In spite of that it’s returning the unique values on the unique sheet after that


    But along with it its pasting all the data in the range DATABASE on sheet data to the right wher u did the calculations


    Can pasting of all the data again be prevented?


    Try clearing the cells where ur doing the calculations on DATA then go to UNIQUE & come back to DATA & see what happens


    U’ll se what I mean



    Hyperdreamz

    Re: COUNTIF data after applying Auto filter


    A quick question


    The file that u sent works like a charm however there’s a little snag


    I noticed that certain calculation are being performed on sheet DATA to obtain the unique data on sheet UNIQUE


    However this (DATA) sheet is updated constantly with a huge cut paste of a dump of an html page


    This will overwrite anything on this sheet


    It’s a paste of pure values resulting in those being effectively erased


    Now this actually works inspire of that but I’m getting all the info on sheet DATA pasted over to the right when the original cell were cleared and the macro re run


    This bloats up the file resulting in a 25-30 MB file when a real sample of the data is used.


    This in turn kills my comp


    Is it possible to move all the calculations to the unique sheet only?


    And prevent the data getting pasted over again


    Sheet UNIQUE must be a stand alone sheet and should not have any reference to any other sheet other than DATA


    The sole function must be to obtain unique values from the given columns on sheet DATA


    Also I would old like to thank you for all the assistance I’m really obliged by the prompt and fantastic solutions ur throwing my way


    It’s all truly appreciated


    Seasons Greetings,
    Hyperdreamz





    .

    Re: COUNTIF data after applying Auto filter


    Thanks a ton man


    That file put me back on track


    However there a small modification I need


    I need it too dump ALL the unique values to a sheet called “Unique”


    Column A = UNIQUE Tech Name
    Column B = UNIQUE Tech Badge
    Column C = UNIQUE Manager Name
    Column D = UNIQUE Fiscal Week Number


    Instead of manually running the macro to generate the lists is it possible to run the macro by just viewing the UNIQUE sheet.


    i.e. the UNIQUE macro must be triggered when a user views the UNIQUE sheet thereby updating the unique values


    For now I’m dropping the upper lower limit on the fiscal week


    I think I’ve found another simpler way to tackle that problem


    Just need a complete list of all UNIQUE values



    Is this possible?





    Seasons Greetings,
    Hyperdreamz

    Re: COUNTIF data after applying Auto filter


    I have received excellent solutions from the people here


    But sadly don’t have a final solution


    I have got bits and pieces from many people that are getting me there


    I sincerely appreciate the effort


    However I’m going to update you guys very soon with my progress



    My current points of failure in the solution are :-




    - My inability to obtain a DYNAMICALLY UPDATED UNIQUE LIST for given parameters. I need these unique lists for the DROP DOWN menus.


    For a given Upper limit fiscal week & Lower limit fiscal week I need a DYNAMICALLY UPDATED UNIQUE LIST of


    1) Tech names
    2) Badge ID’s
    3) Fiscal weeks
    4) Manager Names





    - For a given fiscal week range pull up a DYNAMICALLY UPDATED UNIQUE LIST of manager and the corresponding techs that appear under those individual managers





    Seasons Greetings,
    Hyperdreamz

    Re: Make a dynamic alphabetically sorted UNIQUE list


    Got the plug-in


    Every thing works like a charm


    But is there a way to distribute the plug-in embedded in the file


    Or that particular function


    For e.g. can I extract the UNIQUE VALUES function to say a macro and sent it as a part of a single file????




    Quote from Krishnakumar

    See the attached file.


    Kris

    Re: Make a dynamic alphabetically sorted UNIQUE list


    I actually got the file to open and even got it extracting unique values from multiple columns


    Hover the single resultant sheet for 5000 records was 8MB and froze forever when entering any data


    Is there a way to do the same thing using a DYNAMIC RANGE


    Something that won’t bloat up the file to such un-reasonable sizes and actually open


    On an average 2000-5000 records will be added on a weekly basis


    The calculations bust be instantaneous or at least reasonable


    I can’t seem to get this to work


    Plz help ! !


    I’m ready to even accept a macro at this stage


    Need to jus get UNIQUE ALPHABETIACLLY SORTED VALUES from columns A, E, & F from a sheet called DATA and save those to a sheet called UNIQUE in columns A, B & C respectively without the need to run a macro somthing that will probably auto update the moment u switch between sheets.


    Plz help Ahhhhhhhhhhhhhhh !!!










    Quote from Domenic

    Since I make it a practice not to open attachments, I've attached a sample file for you.


    You'll notice that the formulas have been copied to Row 20 only, to keep the size of the file small. Any new data you enter, up to and including that row, will automatically be updated.


    Hope this helps!

    Re: Make a dynamic alphabetically sorted UNIQUE list


    I tried following ur instruction to T


    But wasn’t able to reach the desired results


    I am attaching a sample sheet I will be working with


    Could you apply the above mentioned formula to the given data?


    I need to achieve the unique list on the sheet SCORE_CARD


    However on a weekly basis new data will be added to sheet DATA


    The unique list must be updated automatically with out the need to repeatedly apply a formula




    Can this be done?



    Seasons Greeting
    Hyperdreamz





    Files

    • sample.zip

      (26.8 kB, downloaded 106 times, last: )