Concatenate if other column value is non-blank or non-zero BUT IN DESCENDING ORDER

Hello,
In column A I have blank cells or numbers (could be zero as well)
In column B I have names, e.g. Peter, John, Mary etc

For example let's have 3, 0, "", 4, 4, 6, 0, 5, "", 1 in column A
and Peter, John, Mary, Kate, Alan, Nick, George, Jenny, Tony, Helen

I am looking for a way cell C1 will give me the names that have a non-zero or non-blank cell next to them BUT IN A DESCENDING ORDER according to the value in column B, separated by commas. That would be Nick, Jenny, Kate, Alan, Peter, Helen
Nick comes first because he has the highest value (6), Jenny comes 2nd because she has the 2nd highest value, Kate and Alan come next...and so on

If it is any help this is the function I have been using but does not deal with the DESCENDING ORDER problem. A function would be great but anything you can come up would also be great

Please Login or Register  to view this content.

Thanks a lot

Please Login or Register  to view this content.

Perhaps a different approach may give you another idea? This simply sorts the data and extracts the data into the string. I have not written it as a function but you could do if required. It is easily modified for Ascending order by changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The disadvantage without a bit of additional work is that it changes the order of your data.

Please Login or Register  to view this content.

Hi Petros,

Welcome to the Forum

Please Login or Register  to view this content.

BTW:=Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

You're welcome!

*Don't forget to put the code tags around your code - just edit your post - highlight the code and click on the #

Hello Bernie. I think I realized a disadvantage. It only works with positive integers. Is there a way around so that it works with all kinds of numbers? Negatives are not a big problem but I need non-integers.
Thank you

Hello again xladept,
your solution is great too but I think it also has one disadvantage.
If I understand correctly it works with one range. That means that the two columns have to be next to each other, right?
I think that it does not work with negative values which is not a big problem at the moment.
For the example it is fine. But for my purpose the columns are not next to each other
Can I define two separate ranges?
Thank you

Give this version a try - tied cells needed to be handled differently... and you can set the limit, either hard coded or using a specific calculated value, like

dLimit = Application.Average(SrcRng)

to only extract values above the average - see the comments in the code....

Please Login or Register  to view this content.

For non-contiguous ranges:

Please Login or Register  to view this content.

*Thanks for the rep!

My point about tied cells was just that they need to be handled differently in code. Last time, we simply counted down the integers from the max value to 0 - but now we need to find the largest, then the second largest, etc. and all the cells that are tied with those values, if any. But if there are ties, then the nth largest and nth +1 largest are the same, so finding all the tied cells would mean finding the same values twice - so my code handles it, just differently. So, if there cannot be ties, then we would handle it differently still, just indexing off the nth largest value and directly finding its corresponding value.

You're welcome and thanks for the rep!