Results 1 to 6 of 6
  1. #1
    Andy_Nel
    Guest

    Sort/Count the number of occurances in a worksheet (97 or 2000)

    I have a list of clients for the quarter and I want to sort by the number of times they have shipped. Each time a company ships, it results in one entry on the worksheet.

    So, I want to generate a report that shows:

    Acme Corporation shipped 23 times
    Omega Corporation 16 times
    etc.

    Am I on the wrong track with the count function?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort/Count the number of occurances in a worksheet (97 or 2000)

    The COUNTIF() function i smore suited to what you are doing. COUNTIF("Range","Acme"), will return to number of occurrences of Acme in the given range. You could designate a cell to contain the Client name you want to report on (say A1), and use COUNTIF("Range",A1).

    However if all your client names are in the same column, you could use the Autofilter and combine it with a SUBTOTAL(3,Range), to get the count of the filtered data.

    Hope that helps

    Andrew C

  3. #3
    Andy_Nel
    Guest

    Re: Sort/Count the number of occurances in a worksheet (97 or 2000)

    Thanks Andrew,

    This does help. All the client names are in the same column.

    What I am looking for is an automatic count for each name in the column instead of one at a time. I would then sort the names from the most frequent shipper to least frequent. Is this possible or is this more suited to a program such as Access?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort/Count the number of occurances in a worksheet (97 or 2000)

    With an additional column, you can achieve what you want without resorting to Access.

    For example if you client names are in column A, you could include the formula in the column beside the existing last column. In the new column (head it Frequency for example) you could place a copy of the formula for each row so that in the first data row the formula would read =COUNTIF(A:A,A2), assuming you are using row 1 for headings, and row 2 id the first row with data. You can copy the formula down to the last row. You should then be able to sort on the new column and by client as the secondary criteria - in descending order. You should then have the list in the order you want.

    Andrwe C

  5. #5
    Andy_Nel
    Guest

    Re: Sort/Count the number of occurances in a worksheet (97 or 2000)

    Andrew,

    That works great! Thanks!

    The only (minor) downside is that sometimes our people will type, "Omega Corporation" and sometimes "Omega Corp.", resulting in two counts. I don't suppose you know of a wildcard option (like the type I can add to a Windows file search) that would be more forgiving? If not, it's no problem. I already have most of what I was looking for....

    Thanks again! Andy

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort/Count the number of occurances in a worksheet (97 or 2000)

    Well now Andy, I think that might prove a little tricky. You could use =COUNTIF(A:A,LEFT(A2,5)&"*"), if the first 5 characters are unique for each client, or change the number (the bigger the better), if different.

    What might be a better bet is to insure that your users use the same client name for each entry by the use of data validation. To do that, creat a list of your clients in a seperate column and sort in alpha sequence (optional but handy). You can then optionally add an input message and and a warning message should an invalid entry be attempted.

    Then select the column where the client name is input, and then from the Data menu select Validation, and in the Allow dropdown list select List, and then give the address of the range of clients you created as the source. Now when your users go to input data, they can select the name from a dropdown list, and the name for each client will always be the same.

    If you have any difficulty implementing that, post back and you will get more assistance.

    Andrew

Posting Permissions

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