# How to put non-repeating text in ascending order

1. ## 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.

2. ## Re: How to put non-repeating text in ascending order

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...

3. ## Re: How to put non-repeating text in ascending order

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

4. ## Re: How to put non-repeating text in ascending order

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).

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

I appreciate any further assistance.

Cheers.

5. ## Re: How to put non-repeating text in ascending order

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!

6. ## Re: How to put non-repeating text in ascending order

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

7. ## Re: How to put non-repeating text in ascending order

Fortunately zeros will not be used in this case!

Let me give it a try!

8. ## Re: How to put non-repeating text in ascending order

Ok, but see my last post!

Cheers

9. ## Re: How to put non-repeating text in ascending order

I saw it! How do I give you the resolution credit?

10. ## Re: How to put non-repeating text in ascending order

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

11. ## Re: How to put non-repeating text in ascending order

I am gonna do it at home! My company proxy is preventing me to do that!

Thanks again!

Cheers

12. ## Re: How to put non-repeating text in ascending order

I'll let you off!

And you're welcome!

13. ## Re: How to put non-repeating text in ascending order

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.

``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.

14. ## Re: How to put non-repeating text in ascending order

Nice stuff, thank you RudiS.

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

#### 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