Posts by s_u_resh

  • Count the number of duplicates in a range and then create a ranked sort using VBA

    Re: Count the number of duplicates in a range and then create a ranked sort using VBA


    Hi norwood,


    In excel 2007, there is readymade menu command:
    Select the data columns


    menu -> data-> remove duplicates


    This will remove the duplicates for you.


    If you have many columns . just select all columns , so the unique is compare all columns,


    If you select only one column, the unique is compare only one column.


    Why need to write codes for things available easily in excel.


    Hope you enjoy this.



    Regards
    S.Suresh

  • Questions on how to use match and multiple criteria to fill multiple cells

    Re: Questions on how to use match and multiple criteria to fill multiple cells


    Dear Deemichelle,


    How do you know , howmany match are available in List.


    So we need to create line id's with counters.


    Then if one id is having five time , we need to check first one or second one ,


    This will give the correct search.



    rb1234.01
    rb1234.02
    rb1234.03



    do the match base on any above one now.


    if you want all the match then : use advance filter in macro. you will get all the available filter in separate list.


    Regards
    S.Suresh

  • Insert 3 lines before a specific text/group, subtotal, then total

    Re: Insert 3 lines before a specific text/group, subtotal, then total


    Hi,


    There is two way to make this solution .


    1. make the subtotal in excel function. To make the subtotal you need to build the dummy column before making the subtotal.


    2. just type 1,2,3 on any one column, copy and paste these three cells to all range.
    Select filter on 3 , and insert rows by visible rows.



    For creating dummy : you need to use the =counta($c$1:c5) formula , so this will give the dummy column ID's


    example like below



    1
    1
    1
    1
    ---
    2
    2
    ---
    3
    3
    3
    3
    3
    ----
    4
    ---
    5
    5
    5
    5
    5
    ---


    Hope you understand the idea.


    Regards
    S.Suresh

  • Multiple Lookup Function in one Formula

    Re: Multiple Lookup Function in one Formula


    Hi GeforceXP,


    Do not try to put all logic in formulas.


    Create master table, from the table you can use only by two forrmulas you can able to do this formula.
    -----------------------------------------------------
    A B C
    Perf 100 200 300
    Zintec 101 201 301
    -----------------------------------------------------
    A 2
    B 3
    C 4
    -------------
    vlookup(perf,range,vlookup(A,range,2,0),0)


    Use name ranges if required in formulas as indirect.



    Regards
    Suresh
    Mumbai

  • Macro to build a daily trend list

    Re: Macro to build a daily trend list


    Hi Amsterdam,


    As i have seen your requirement is very normal requirement is MIS development. First how you are managing your datasheet?


    Are you keeping your data sheet in excel or any database something like access or SQL server.


    What you need to do is convery your excel sheet data to database format to new excel sheet.


    From that database sheet you can create pivot table , or you can use the database to write VBA code via ADO or formulas.


    this will make your life very easy for different months and years to Manage this one single program.



    Best of Luck


    Suresh
    Microsoft excel Developer
    Mumbai

  • Stopping Loop on empty cell

    Re: Stopping Loop on empty cell


    you can use for loop is much better.


    Code
    for i = 4 to range("A65536").end(xlup).row


Range("A"& i).value = value to pass


next


    by doing this we can control the number of rows in excel.


    hope this helps you.

    Suresh

  • Hide & Show Columns Macro

    Re: Hide & Show Columns Macro


    Dear Pat,


    Excel macro done not support murged cells for some cases, so please do not use merged cells , your problem will be solved.



    Regards
    Suresh


    Excel VBA programmer
    Mumbai. India

  • Re-layout Data To Flat Line Database

    Re: Re-layout Data To Flat Line Database


    Hi Gold,


    You need to write macro using Text to column method. It is easy to write a small macro.


    I think your software gives the output of text file. If you provide the actual test file i can write the macro for you.


    Please give me 2 or 3 different attachments so i can check my macro in your attachments.


    If you have access from database you can also get the inforamtion from database.


    Let me know if i can be any help for you ..


    Regards
    Suresh


    Mumbai. India

  • Sort Horizontally Then Vertically

    Re: Sort Horizontally Then Vertically


    Dear joseph,


    I would advise you store your data in separate worksheet like normal data. so you can sort as you required by excel.


    Second, for printing as 3 columns like label format. You can use VBA code to print.
    If you do not know VBA code then you can also use formulas and prepare report format.


    Example - use Vlookup formula with the coresponding serial number.



    1) aaaa address city
    2) bbbbb address city
    3) ccccc addess city



    Report Fotmat


    vlookup( 1 , range,no,0) , vlookup( 2 , range,no,0) , vlookup( 3 , range,no,0) ,



    By doing this you can get the required format and also the required sorting type.


    Hope this will help you.


    Regards
    Suresh


    Mumbai.India

  • OWC & ASP on Webpage

    I am developing website which use excel sheets. I want good example to use OWC with asp code. any help please.....


    S.Suresh
    Excel VBA programmer
    Seepz, Mumbai.


    Edit by admin to remove email.

  • Lookup & Cross Match

    Re: Table Entry Macro


    Hi,


    I am trying to answer your query but it is confusing. Can you explain what you want output.



    Regards


    S.Suresh
    Excel VBA programmer
    Seepz, Mumbai.