Count multiple values and arrange HIGHEST TO LOWEST.

[edwardtong694]

Hi Guys,

Bit of a strange question this time but I hope someone can help.

I have a list in excel which I cannot determine the values before it is created. As this will differ each time data is copied into the template.

What I would like to do is create a formula which will automatically populate, two columns of data one containing the unique values entered into Cell A1 and then another column next to it with a count of the amount of times the values occur.

I would also like the data to be shown so it is arranged by the highest count at the top to the lowest.

Is this possible?

Thanks Ed

 

[KB_Miner]

Yes, it's possible. Thinking that what you'll eventually want will be a macro, not a formula. Basically, what you'll want to do is after pasting the data in column A, go up to the Data tab at the top and near the filter button is another button called Advanced. Click on that. Hit the Copy to another location radio button. Select your data, leave the Criteria range blank, and then the Copy to range should be column B. Check the Unique records only box. This will give you the unique values. In column C, use a COUNTIF formula to count the values in column A based on the values in column B (so if you have headers, your formula in C2 would be =COUNTIF(A:A, B2). This formula can be copied down and then you can sort columns B and C based on highest to lowest in Column C.

 

[AlexaS]

You can do a formula something like this in column A to pull in the data initially from your downloaded list: =IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)=1,Sheet1!A1,"")
-I made an assumption that these two columns are on a different sheet, just change as necessary

And then a formula like this in column B with a count function: =Countif(A1:A100,A1)

Without a macro, you'll have to sort manually.