Find and list corresponding values in a column in descending order

[sans]

Hello,

I have been trying for a while to get this to work with no success. I'd like to create a list of values and their percentages in a column in descending order. If any index values have the same percentage, order is not important.

Thank you,Sans

[sans]

I've re-edited the attachment.Thanks

[BarryTSL]

To sort in order, copy the 2 rows and paste with the Transpose so they are in 2 columns then do a custom sort by the values column.

[sans]

Hi Barry, That approach is not very convenient as I have lots of data whic I need to process and this data also keeps changing. Thanks,Sans

[micope21]

Hi

Please click on attachment

Good luck.

[sans]

Hi micope21, That's what I was looking for. Many thanks! Sans

[icestationzbra]

solution approach updated in post #12 below.

[sans]

Thanks, icestationzbra. Yes, if the percentage of any two values is exactly the same, then there is a duplicate listing.

i.e.

C 5.3%
C 5.3%

instead of

C 5.3%
D 5.3%

Maybe there is a faster workaround to this. Thank you,Sans

[icestationzbra]

i am unable to understand - did you want distinct values for duplicate percentages, or not?

for example, in the sheet that i uploaded in my post (#7), N40 and N41 both have 0.2%, but M40 and M41 have Y and Z corresponding to them. did you want that to happen or did you just want Y repeated twice?

if you just wanted Y repeated twice, @micope21's uploaded file does that already.

[sans]

No, you got it right. It should be Y 0.2% and Z 0.2%, as it is in your spreadsheet. I was just wondering if there is a simpler way of doing this without using helper columns, by modifying the formula in micopes attachment. I've tried a few ideas but haven't got it to work so far. Thanks, Sans

[icestationzbra]

sans, i simplified the solution further and updated the file in post #7, but i have not been able to completely avoid the use of helper column. if i figure something out, i will revert here.

[icestationzbra]

sans, here you go - the solution 'sans' helper column...

[sans]

Cool thanks! I'll re-mark the thread as unsolved though, possibly someone has a different solution to share.

[micope21]

Hi

Sorry about duplicate. I didn't notice.
Not much different between my and icestationzbra forumla. I might as well use his. Make it easy for you instead use Name Manager. On my file put this cell M18 =INDEX(M$2:AN$2,1,SMALL(IF($M$14:$AN$14=N18,COLUMN($M$14:$AN$14)-MIN(COLUMN($M$14:$AN$14))+1),COUNTIF($N$18:N18,N18))) confirmed with CONTROL+SHIFT+ENTER then copy down

This will make you understand if you put in other file.

[sans]

Hey guys, now it works great. Thank you both very much for the replies and help! Sans