Sorting data within a cell by magnitude of percentages

[meb229]

Hi,

I have 18,000 cells with data like the following: Apple (10%); Banana (30%); Cherries (60%)

I want to create an adjacent cell that will automatically rearrange it to look like this: Cherries (60%); Banana (30%); Apple (10%).

Is there any way to do that?

Thank you in an advance!

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[meb229]

Sorry about that. Here's an example of what I have and what the goal is.

[alansidman]

Using Power Query/Get and Transform found on the Data Tab

Here is the Mcode to get your expected results.

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[meb229]

Thank you! Is there an easy way to concatenate the results so that I can get them all into one cell?

[jindon]

UDF

Use in cell like
=SortPerc(A2)

Please Login or Register  to view this content.

[meb229]

Thank you! It works with my initial test data but then when I went to apply it to another cell, which has cities instead of one word items, it seemed to get thrown off.

Here is my initial data:
Boca Raton (4%); Boston (12%); Chicago (6%); Los Angeles (18%); New York (53%); Washington, DC (7%)

Here is what came up after using SortPerc:
Washington, DC (7%); Chicago (6%); New York (53%); Boca Raton (4%); Los Angeles (18%); Boston (12%)

I feel like the answer is obvious but I am not the best with UDFs so I can't figure out what to tweak to adjust the formula. Can you please tell me what change to make? Thank you again for all your help!

[jindon]

If you upload all the possible pattern and the result that you want in a workbook, I can work from that.

[meb229]

Thanks! See attached.

[jindon]

Did you miss "Boca Raton (4%)" in your result?

try change to

Please Login or Register  to view this content.

Edit; the line in bold.

[meb229]

Oh shoot I did; good catch. That one should have been last, sorry.

I'm still getting the same results. I've attached the workbook again.

[jindon]

Because you are still using the 1st code I posted.

You need to replace it with the one in my post #10

[meb229]

So weird I thought I kept copying over it but I guess I was not. It worked perfectly in a new book. Thank you so much!!

[jindon]

Yeah, it also happens to me sometime.

You are welcome and thanks for the rep.

[alansidman]

An alternative with Power Query

Please Login or Register  to view this content.