How to put non-repeating text in ascending order

Hi folks!

Does anybody know how to ascendantly sort groups of numbers within a cell?

For instance, in a cell I have the numbers "5417" and after sorting it would come out as "1457"

Any help would be appreciated thanks

Cheers.

Hi,

I'll be honest to say I cannot assist myself but in the attempt to solve this I am bouncing it off some other folks in another forum to see if I can help resolve this for you. I will of course credit te person and the forum if I can provide a solution for you...

Hi,

Not quite sure as you only give one example, but perhaps this array formula**:

=SUM(SMALL(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

Could you also provide a link to the other forum post?

Regards

Hey XOR!

You're always saving my skin! haha...

Thanks, it worked like a charm!

The only thing is that when the number repeats, I would like to cut it off.

E.g.:

A1
5417146

B1(result expected)
14567

Thank you so much and please, provide me information on how to give you the solution credit. You helped me with another issue and so on I haven't given it to you!

Thanks again!

Cheers.

EDIT: Actually, my original post was:

Hi folks!

I want to create a text which contains the "profile" of an order.

As an e.g. I attached a file which shows what I do need.

I want the column K cells to show the string "1457", which i put manually.

This "1457" string consists of column I values, non-repeating and ordered ascendantly when the column B cell value is the same.

E.g.: Pre-order 218545 starts at b2 and goes until b31, then it has to pick all the corresponding values from column I and put it as one string which doesn't repeat values and is ordered ascendantly(cell column J).
Pre-order 218716 starts at b32 and goes until b52, then it has to pick all the corresponding values from column I and put it as one string which doesn't repeat values and is ordered ascendantly(cell column J).

The example spreadsheet can be found on this link: http://s000.tinyupload.com/index.php...42927291848173

I apologise that I could not upload it using the forum upload tool.

I appreciate any further assistance.

Cheers.

Hi,

I'm afraid that link isn't working (?)

To answer the question re unique integers only, again, array formula:

=SUM(SMALL(IF(FREQUENCY(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW($1:$10)-1),ROW($1:$10)-1),ROW(INDIRECT("1:"&SUM(0+(FREQUENCY(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW($1:$10)-1)>0)))))*10^(SUM(0+(FREQUENCY(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW($1:$10)-1)>0))-ROW(INDIRECT("1:"&SUM(0+(FREQUENCY(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW($1:$10)-1)>0))))))

though zeroes cause errors, so let me know if they will be a feature, and what you would like the result to be in those cases.

Cheers!

Sorry - that was rubbish.

Much better is:

=SUM(SMALL(IF(ISNUMBER(FIND(ROW($1:$10)-1,A1)),ROW($1:$10)-1),ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1)))))*10^(COUNT(FIND(ROW($1:$10)-1,A1))-ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1))))))

Regards

Fortunately zeros will not be used in this case!

Let me give it a try!

Ok, but see my last post!

Cheers

If you really want, you can click the * Add Reputation button!

I'll let you off!

And you're welcome!

Originally Posted by XOR LX

Could you also provide a link to the other forum post?
Regards

HansV from Eileen's Lounge has graciously provided a VBA function solution.
See the thread and his reply here.

Please Login or Register  to view this content.

Place both macros in a standard module and in cell K2 of the sample file you posted, type: =SortUnique($B$2:$B$52,$I$2:$I$52,B2) and autofill down.