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

1. ## 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

2. ## Re: Look for values in a list, count and list in descending order

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.

3. ## Re: Look for values in a list, count and list in descending 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

4. ## Re: Look for values in a list, count and list in descending order

Although it may not give exactly the format you want, a pivot table is probably the quickest way to do this as it will give you your list of unique values and the counts. Are you familiar with them?

Edit: or a VBA approach:
``Please Login or Register  to view this content.``

5. ## Re: Look for values in a list, count and list in descending order

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

6. ## Re: Look for values in a list, count and list in descending order

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

8. ## Re: Look for values in a list, count and list in descending order

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

9. ## Re: Look for values in a list, count and list in descending order

Have you tried mine, too ?

10. ## Re: Look for values in a list, count and list in descending order

Hi jindon,

@ 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

11. ## Re: Look for values in a list, count and list in descending order

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

12. ## Re: Look for values in a list, count and list in descending order

Try this anyway...
``Please Login or Register  to view this content.``

13. ## Re: Look for values in a list, count and list in descending order

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%

14. ## Re: Look for values in a list, count and list in descending order

This will output directly to the cells and format the cells for % correctly.
``Please Login or Register  to view this content.``

15. ## Re: Look for values in a list, count and list in descending order

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

16. ## Re: Look for values in a list, count and list in descending order

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

17. ## Re: Look for values in a list, count and list in descending order

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

18. ## Re: Look for values in a list, count and list in descending order

Try change
``Please Login or Register  to view this content.``
to
``Please Login or Register  to view this content.``

19. ## Re: Look for values in a list, count and list in descending order

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

20. ## Re: Look for values in a list, count and list in descending order

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

21. ## Re: Look for values in a list, count and list in descending order

This should do it.
``Please Login or Register  to view this content.``

22. ## Re: Look for values in a list, count and list in descending order

Amazing, thanks a million jindon! Sans

23. ## Re: Look for values in a list, count and list in descending order

No worries and enjoy the stats.

24. ## Re: Look for values in a list, count and list in descending order

I've run into a minor issue. If for example one of the items looks like this 3-7-15 when calculated the results looks as 45846 for some reason, something completely irrelevant. I tried formatting all cells (both the items and data) as text before running the macro but the issue is still there. I think it sees 3-7-15 as a date or something else... Thanks, Sans

25. ## Re: Look for values in a list, count and list in descending order

I just double checked and the same thing happens with the macro in post #7 though I get correct results with the macro in post #4. The problem is with the items, as it it reads (some of) them as something else and when they are compared with the data column I get the name of the item listed wrong with incorrect results. Thanks,Sans

26. ## Re: Look for values in a list, count and list in descending order

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.

27. ## Re: Look for values in a list, count and list in descending order

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

28. ## Re: Look for values in a list, count and list in descending order

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.

29. ## Re: Look for values in a list, count and list in descending order

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

30. ## Re: Look for values in a list, count and list in descending order

jindon, sorry to bother you again but I have one quick question. What do I have to change in the macro if I want to output relative references in the count formulas? (Or I can have two macros one with relative and another with absolute refs ) Thank you, Sans

31. ## Re: Look for values in a list, count and list in descending order

``Please Login or Register  to view this content.``

32. ## Re: Look for values in a list, count and list in descending order

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

33. ## Re: Look for values in a list, count and list in descending order

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

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

#### 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