Array Formula that will alphabetically sort and remove blanks but keep the duplicates

Try this array entered formula*, it sorts, removes blanks and leaves duplicates:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If the formula doesn't do what you want I would suggest uploading a spreadsheet that shows the unsorted data and then a manually produced column showing how you would like it to be sorted, so that we may attempt to duplicate the manually produced results using formula(s) and/or code.
To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Thank you JeteMC for your code. I was wondering if I could take it a step further and apply this formula to cell that references to another workbook. When I apply this code onto cells that reference to another workbook, I would get blanks first then later the sorted data. I attached an example of what I am doing. Is there a way to get the sorted data first?

In the sort.xlsx, Sheet1 A1:H15 is the original inputted data. In Sheet2, A1:H15 is reference to Sheet1's A1:H15 and Sheet2's J1:J739 is the code you provided. J1:J86 are blanks but starting from J87:J120, I get the sorted data.

Thank you

I believe that the reason for the blanks in column J was because the formula is referencing cells with formulas in them.
When you create a table (tbl) from the range on sheet 1 and use the formula from post #2 in column J of sheet 2 to reference that table the first value in the column is 'a' as expected.
The formula will work with regular range references (i.e. Sheet1!A$2:H$16) also, as is modeled in column K on sheet 2.
Let us know if you have any questions.

Dear RXcel,
In you attached file both the sheets looks like same

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

E4=TEXT(INDIRECT("R"&TEXT(RIGHT(LARGE(INDEX(0+(COUNTIF($A$4:$C$100,">="&$A$4:$C$100)&TEXT(ROW($A$4:$C$100),"0000")&TEXT(COLUMN($A$4:$C$13),"000")),0),ROWS(E$4:E4)),7),"0000\C000"),0),"#")

Please Login or Register  to view this content.

Try the above in Your Master file copy towards down

Thanks for adding rep.
if it takes care of your post please Don't forget to mark this thread as "Solved" (Thread Tools->Mark thread as Solved)