• 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

D

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.
 
I would recommend creating a sample file and attach it so that people can work on it and help you in a timely manner.
 
If you have the dynamic array functions, how about
=SORT(UNIQUE(FILTER(Sheet1!H2:AJ2,Sheet1!H2:AJ2<>"")),,,1)
 
Fluff13 said:
If you have the dynamic array functions, how about
=SORT(UNIQUE(FILTER(Sheet1!H2:AJ2,Sheet1!H2:AJ2<>"")),,,1)
Click to expand...
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??
 
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

  • Example1.xlsx
    11.7 KB · Views: 8
shrivallabha said:
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.
Click to expand...
shrivallabha,
I tried your formula and It does nothing, no errors though?? I'm using MS Office Home Student 2016. What is CSE?
 
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
 
You must log in or register to reply here.
Back
Top