• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Put a string or row of numbers in order, no duplicates

dwrowe001

Member
Hello everyone,
If I may ask to pick your brains.. I would like to take a string of numbers in a row, column H to AJ on sheet 1, the row contains random numbers between 1 to 45, and has duplicate numbers. the current order is haphazard, no order.. and there is the occasional blank or empty cell here and there, totally random. I would like to take all those numbers in the row, columns H to AJ and have them copied to a different sheet, say sheet 2 and put the numbers in numerical order, low to high, and remove all duplicate numbers.

If at all possible I would like an Exel formula. I can deal with formulas, I'm no expert but if I see a formula I can usually figure it out... usually.. I just can't come up with complex ones from scratch on my own.. and forget VBA or macros... I can't figure those out at all...
I appreciate your help with this.
 

Chief Ace

Member
I would recommend creating a sample file and attach it so that people can work on it and help you in a timely manner.
 

Fluff13

Active Member
If you have the dynamic array functions, how about
=SORT(UNIQUE(FILTER(Sheet1!H2:AJ2,Sheet1!H2:AJ2<>"")),,,1)
 

dwrowe001

Member
If you have the dynamic array functions, how about
=SORT(UNIQUE(FILTER(Sheet1!H2:AJ2,Sheet1!H2:AJ2<>"")),,,1)
Hi Fluff13, I tried entering the above formula into cell E3 on Sheet2, then Cntrl-Shift-enter to make it array, but it said it was invalid??
 

shrivallabha

Excel Ninja
If UNIQUE function is available to you then you can use below approach.
=IFERROR(SMALL(UNIQUE(("0"&Sheet1!$H2:$AK2)+0,TRUE,FALSE),COLUMNS($A$1:A$1)),"")

I have just tested against posted sample. See attached file. Depending on your version you may have to use CSE.
 

Attachments

dwrowe001

Member
If UNIQUE function is available to you then you can use below approach.
=IFERROR(SMALL(UNIQUE(("0"&Sheet1!$H2:$AK2)+0,TRUE,FALSE),COLUMNS($A$1:A$1)),"")

I have just tested against posted sample. See attached file. Depending on your version you may have to use CSE.
shrivallabha,
I tried your formula and It does nothing, no errors though?? I'm using MS Office Home Student 2016. What is CSE?
 

bosco_yip

Excel Ninja
Try,

In "Sheet2" E4, copied across right :

=IFERROR(AGGREGATE(15,6,Sheet1!$H$2:$AK$2/(COUNTIF($D4:D4,Sheet1!$H$2:$AK$2)=0),1),"")

71168
 
Top