1

I need help to sort out a list of agencies. I have now gotten the frequency for each agency but I need them to match.

Then, I need to sort out this list from Largest to Smallest frequency. That means I need to find out the Agency name (first) with the highest frequency and how many times did they appear. Then second, third, and so on and so forth.

In the frequency column, the formula I have used is: =COUNTIF([Agency],A2).

My problem is: I need all Agencies A, AA, Aa, etc. to match though. How do i do this? Then sort them out from largest to smallest.

Note: I need this to run as a formula as I need to put this in a table in a different worksheet. For this case, this workseeht is named: 2018 Data, then the table I want to create with the list I am thinking of will be placed in 2018 Reporting. So, I am unable to use the manual sorting and filtering. I hope this info helps.

enter image description here

Improve this question

1 Answer 1

Reset to default
0

  1. Titles in First Row : Cell A1 = "Agency", B1="Frequency"

  2. Data Given is entered From A2="AA",....A42="j" ; B2= 2, .....B42= 4.

  3. Copy The Agency Column A2:A42 in Cell Say, A50..

  4. Keep Paste Area Selcted; Go to DATA Menu, Select " REMOVE DUPLICATES" With this, Only 17 Unique Values Of Companies are left From A50 To A66.

  5. In B50 enter the Formula =SUMIF($A$2:$A$42,A50,$B$2:$B$42)

  6. Copy Formula B50, down the column upto B66; Giving Total Frequency for each Company.

  7. Copy the Area A50:B66, and Paste, Special, VALUES, in Cell E50.

  8. Sort the Table E50 : F66 On Total Frequency, Highest to Lowest On Frequency.

There can be still better approach then this. I have given Long Method Solution.

I hope, may be useful to you.

THANKS, For Providing Opportunity to Serve.

Improve this answer
2
  • Hi, I have tried it now on my list and it is counting wrongly the frequency wrongly. In my column B, "-" counts 5. But after pasting your formula, it now shows 25 (5*5). I have now gotten the list sorted out, but counting the frequency for each is still wrong. Also, it doesn't take into consideration that AA, A and As should have been counted together, it is still getting counted individually instead of together. Any further help? But for whatever it is worth, thank you for your help, regardless. :-)
    – Jennifer
    Commented Feb 9, 2021 at 20:27
  • Very Sorry. Let me go through it. I shall revert back. Excuse me for the inconvenience suffered by you. Thanks.
    – Harshad Amichand Vegda
    Commented Feb 17, 2021 at 11:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .