Look for values in a list, count and list in descending order

Hello,

If anyone is able to help me with the following problem would be amazing. I have a set of values in a list, and would like to check those values against a column of data, count and list them in descending order. If an item from the list is not found in the column of data, I would still like the value to be listed with a count of 0.

I am attaching an example spreadsheet. The column on the left is a small part of the actual data, and on the right a small example. Preferably, a UDF would be ideal as I have tens of sets of data that keep changing and many different values to be checked spanning across multiple columns. The actual data is around 5000 rows with approx. 100 values to be checked against the data.

I would prefer to get the results as in the attachment but if thats out of reach and is more convenient to put the results in a different manner, please do.

Thank you for looking,
Sans

I'm a bit confused by your data. What is the relationship between column C and I? And is the desired output in I51 going down? I'm not sure a UDF could arrange them in order.

Hey Stephen,

Sorry for the confusion. Column C is completely irrelevant to columns C and I. I just wanted to show what the actual data looks like i.e. that its not a single number.

I made the example a little clearer.

Thank you,Sans

I am familiar with them in the sense that I've seen their function in a couple of other spreadsheets (not mine). However, I need something extremely flexible as I will need to insert/move/delete thousands of rows of data all the time, delete & insert columns...so I would prefer it to be in the format as in my original post. If a solution is possible using standard excel functions, its ok with me.

Thank you, Sans

Well if your values are listed somewhere as in your example you could use the code solution. If not, would need to add something.

Try this one


CountValfromlist.Desce.xlsm

Hi Stephen,

I can't believe how fast you wrote that code.

Your macro functions exactly how I wanted it to work! :D This way it is super flexible, the results are not affected by other changes and I can mix and match easily the thousands of data and the values. Many many thanks Stephen! Exactly what I needed! Sans

Have you tried mine, too ?

Hi jindon,

Sorry, I just saw your reply.

@ Stephen and jindon: If its ok with both of you guys, and its also possible, can the functionality of your macros be mixed into one macro? Be able to choose the values and data as in Stephen's macro (this is the only way I can analyze this much data and having to move them around, and mix and match easily) - but get the results as in jindon's macro with the data,count,(space underneath) and then the following data,count etc...The space is useful as in jindon's macro as I will need to place the % right underneath the results. Before (with Stephens macro) I was going to insert an empty row with a formula right between all the results. If this can be done straight away though it would be great.

Thank you, Sans

What kind of %? to be inserted in the blank?

Try this anyway...

Please Login or Register  to view this content.

If the data is 5000 rows long, and the count of i.e. number 1 is 250, then the percentage is the count against the number of data, calculated as a two decimal % i.e. 0.0%

This will output directly to the cells and format the cells for % correctly.

Please Login or Register  to view this content.

That's simply amazing! :D

Is there anywhere in the macro where I can set up the decimals for the percentages? Or that is the default? Curently its showing, for example, 0.214285714. The list of counts is usually 100-200 values, if its possible anywhere in the macro to change the decimals accordingly i.e. to 2.1 or 2.14 it will be even better, otherwise I can do it manually. Thank you,Sans

Ok, I just saw your reply. I think I get a delay in seeing the replies. I am checking it out now. Thanks,Sans

Amazing! Thank you very much. Could not have asked for anything better. Many many thanks to both of you guys for all the help! Sans

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Excellent, thanks! I was wondering why before even though it was displaying 0.0% in the results - in the formula bar it was displaying the percentage with 13 decimal places. Thank you, Sans

I was wondering if its not too much trouble, to get a slightly modified version of the macro where the results are displayed a little differently. When the items number is small the current macro works better, but for larger amount of items, I think the one in the attachment will work better.

Thank you, Sans

This should do it.

Please Login or Register  to view this content.

Amazing, thanks a million jindon! Sans

No worries and enjoy the stats.

Excel read such data, 3-7-15, as DATE and the result, 45846, is converted to its numerical value.

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Note: Now the code outputs all the data as Text.

Yes, now it works great! Thank you very much jindon!

I have one quick question. I noticed in one of the previous macros, I think it was the first one stephen had posted, that when the macro was ran, a countif formula was also placed in the count results. If it is possible to implement this in the macro it would speed up my workflow even more as if I change only a few values (when the % have a big gap between them and the descnd. order won't change) I can quickly run and update the one count only. Thank you for your help! Sans

Do you mean like this?

Please Login or Register  to view this content.

Edit:
Calculation mode in your attached file was set to manual, so you need to change it to automatic.

That's exactly what I meant, thank you jindon! Yes I am aware of the calculation mode, I have it to manual as the size of my worksheet is massive and use a macro to calculate only selected cells. Thank you very very much for all your help! Sans

Not sure about "relative reference"...

Please Login or Register  to view this content.

Thanks jindon, it works great! Now I can easily change the range or criteria of the countif formula into relative or absolute. Amazing, thank you! Sans

Hi jindon,

Would it be possible to help me with another version of the above macro so that the counted values are listed simply as:

Item
count
%

Currently they are listed as

Item Count

and below the count the %.

I tried to modify it but I've spend the last few hours without managing to do so.

Thank you,
Sans