+ Reply to Thread
Results 1 to 14 of 14

Macro to sort and get count

  1. #1
    Registered User
    Join Date
    06-25-2009
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    76

    Macro to sort and get count

    attached is the sample excel

    refer the INPUT tab. there are 3 columns.

    first we need to sort by state column.

    then within the same state we need to find the number of columns (N) who have same value for col1

    after we find that we need to print - (N) against the names in the same row of col 2 ;

    I require this urgently. it would be nice if you can help.
    Attached Files Attached Files
    Last edited by singhabhijitkumar; 05-10-2010 at 03:36 AM.

  2. #2
    Registered User
    Join Date
    06-25-2009
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    76

    re: Macro to sort and get count

    i require this macro urgently. please help.

  3. #3
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    re: Macro to sort and get count

    Ignore this, see below post.
    Last edited by Dulanic; 05-10-2010 at 11:51 AM.

  4. #4
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    re: Macro to sort and get count

    Here is some code without updating any other cells like I did with the last code:

    Please Login or Register  to view this content.
    Last edited by Dulanic; 05-10-2010 at 11:48 AM.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    re: Macro to sort and get count

    Tip of the day:

    Since data sometimes get unexpected blanks in the middle of the data, you might STILL want to find the lastrow of actual data, skipping over those blanks. So, instead of starting at cell A1 and jumping down to the last row (it stops at the first blank cell, which might be a blank in the middle of the data...oops)...instead, start at the bottom of the worksheet and jump UP to the last row. This will always find the last row of data in that column.

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: Macro to sort and get count

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    Everyone's requests are urgent to themselves, so be patient & use the waiting time to read the Forum Rules.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    06-25-2009
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Macro to sort and get count

    is it possible to do the same using formula

  8. #8
    Registered User
    Join Date
    06-25-2009
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Macro to sort and get count

    not working. could u embed the macro in excel and post.
    Quote Originally Posted by Dulanic View Post
    Here is some code without updating any other cells like I did with the last code:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Macro to sort and get count

    See attached file, macro is setup on the workbook. The output tab is empty, just run the macro and it will do as requested.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Macro to sort and get count

    I looked at your request again, am I wrong that your results you provided don't match what you asked for? It looks like you sorted the #s in Col 1 and Col 2 seperately? That doesn't make sense to me, as it looks like you sorted it by B first, then A and C together? but each column at a time? The data isn't staying in line with what was originally there.

    Why does Amey orginally show a 3 in col1 then it get's changed to a 1 in your output?

    Input:
    MH 3 Amey

    Output:
    MH 1 Amey-3

    The way you wrote your request, it would seem my output is what you asked for, but it doesnt match your result.

    MH 3 Amey-1
    Last edited by Dulanic; 05-11-2010 at 10:22 AM.

  11. #11
    Registered User
    Join Date
    06-25-2009
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Macro to sort and get count

    i am getting error for statement c.Value = c & "-" & WorksheetFunction.CountIfs(Range("B2:B" & LastRow), c.Offset(0, -1), Range("A2:A" & LastRow), c.Offset(0, -2))

    Run-time error 438:
    Object doesn't support this proeprty or method.

    I am using MS Office 2003
    Last edited by singhabhijitkumar; 05-12-2010 at 12:10 AM.

  12. #12
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Macro to sort and get count

    That would be why, I would update your profile to say 2003 instead of 2007. I can't look at this now, but I will once I am back (at work currently).

  13. #13
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Macro to sort and get count

    Well, I am going to be honest, I am not sure how to apply a sumproduct to the cell. COUNTIFS does great and I can work that into VBA, but SUMPRODUCT which I think would be the main way to get this to work... is not so easy to work into VBA. Maybe someone else can assist with that line.

  14. #14
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Macro to sort and get count

    Well I got the answer. I did some research and found that evaluate would be the best route to go towards getting sumproduct to work with this. I know it would take sumproduct, but wasn't sure how to quite work that in for the evaluate. I got some assistance in writing that portion and got this working. Here is the code, but again this is based off what you asked for, which does not match the results you provided. And to give credit where credit is due, Dave Peterson was able to help me with implementing the sumproduct.

    Please Login or Register  to view this content.
    Last edited by Dulanic; 05-12-2010 at 09:40 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1