+ Reply to Thread
Results 1 to 33 of 33

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

  1. #1
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    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. #3
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    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.
    Last edited by StephenR; 04-19-2012 at 07:26 AM.

  5. #5
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    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.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

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


  8. #8
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

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

    Have you tried mine, too ?

  10. #10
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

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

    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

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

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

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

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #13
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #15
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #16
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #17
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #19
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #20
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #22
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

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

    Amazing, thanks a million jindon! Sans

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

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

    No worries and enjoy the stats.

  24. #24
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Last edited by sans; 04-19-2012 at 03:00 PM.

  25. #25
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Last edited by sans; 04-19-2012 at 03:25 PM.

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #27
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

    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. #29
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #30
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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
    Last edited by sans; 04-20-2012 at 07:29 AM.

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,337

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

    Not sure about "relative reference"...
    Please Login or Register  to view this content.

  32. #32
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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. #33
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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