Unique Sorting along row

J

jumpulas

Board Regular
Joined
Sep 18, 2004
Messages
80
Hello all,

I have the following:
In Row A1:H1
AA CC BB AA AA CC DD

There are blanks as well
What I would like to show is that these are uniquely sorted along the row and the result displayed in same row but starting in cell J1;
So the result in cells J1:M1 should be AA BB CC DD

Hope I have explaned the question well, I am not sure how to copy and paste here a selection of the spreadsheet to you show.

I have seen solutions if the data is columnwise and can't get it work for rowwise
Thanks.
Jumpulas
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Mar44
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            .Item(Dn.value) = Dn.value
         [COLOR="Navy"]Next[/COLOR]
            Ray = .keys
[COLOR="Navy"]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] J = i To UBound(Ray)
        [COLOR="Navy"]If[/COLOR] Ray(J) < Ray(i) [COLOR="Navy"]Then[/COLOR]
            Temp = Ray(i)
            Ray(i) = Ray(J)
            Rng(J) = Temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] J
[COLOR="Navy"]Next[/COLOR] i
Range("J1").Resize(, .Count) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks for the code, but I would prefer excel formula based solution if there is one.

Thanks
Jumpulas
 
Upvote 0
This solution assumes all the values are two occurrences of the same letter (e.g. AA, BB, etc). It also requires you to enter the formulas in J1 and K1 use Control-Shift-Enter. The formula in K1 can be copied to L1 through P1.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>AA</TD><TD>CC</TD><TD>BB</TD><TD>AA</TD><TD>AA</TD><TD>CC</TD><TD>DD</TD><TD></TD><TD></TD><TD>AA</TD><TD>BB</TD><TD>CC</TD><TD>DD</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J1</TD><TD>{=REPT(CHAR(SMALL(CODE($A1:$G1),COLUMN(A1))),2)}</TD></TR><TR><TD>K1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(J1),CODE($A1:$G1)),1)),""),2)}</TD></TR><TR><TD>L1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(K1),CODE($A1:$G1)),1)),""),2)}</TD></TR><TR><TD>M1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(L1),CODE($A1:$G1)),1)),""),2)}</TD></TR><TR><TD>N1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(M1),CODE($A1:$G1)),1)),""),2)}</TD></TR><TR><TD>O1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(N1),CODE($A1:$G1)),1)),""),2)}</TD></TR><TR><TD>P1</TD><TD>{=REPT(IFERROR(CHAR(SMALL(IF(CODE($A1:$G1)>CODE(O1),CODE($A1:$G1)),1)),""),2)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
The following sorts text and/or numerical values, and allows empty/blank cells...

I1, confirmed with CONTROL+SHIFT+ENTER:

Code: 
=SUM(IF(FREQUENCY(IF(A1:G1<>"",MATCH("~"&A1:G1,A1:G1&"",0)),COLUMN(A1:G1)-COLUMN(A1)+1)>0,1))

K1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

Code: 
=IF(COLUMNS($K1:K1)<=$I$1,INDEX($A$1:$G$1,MATCH(SMALL(IF($A$1:$G$1<>"",IF(ISNA(MATCH($A$1:$G$1,$J$1:J1,0)),MMULT(COLUMN($A$1:$G$1)^0,(IF($A$1:$G$1<>"",$A$1:$G$1)>TRANSPOSE(IF($A$1:$G$1<>"",$A$1:$G$1)))+0))),1),MMULT(COLUMN($A$1:$G$1)^0,(IF($A$1:$G$1<>"",$A$1:$G$1)>TRANSPOSE(IF($A$1:$G$1<>"",$A$1:$G$1)))+0),0)),"")
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Mar44
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            .Item(Dn.value) = Dn.value
         [COLOR="Navy"]Next[/COLOR]
            Ray = .keys
[COLOR="Navy"]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] J = i To UBound(Ray)
        [COLOR="Navy"]If[/COLOR] Ray(J) < Ray(i) [COLOR="Navy"]Then[/COLOR]
            Temp = Ray(i)
            Ray(i) = Ray(J)
            Rng(J) = Temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] J
[COLOR="Navy"]Next[/COLOR] i
Range("J1").Resize(, .Count) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
Click to expand...

It looks like there's a typo. I think this line...

Code: 
Rng(J) = Temp

should be replaced with

Code: 
[COLOR="Red"]Ray[/COLOR](J) = Temp
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Mar44
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            .Item(Dn.value) = Dn.value
         [COLOR="Navy"]Next[/COLOR]
            Ray = .keys
[COLOR="Navy"]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] J = i To UBound(Ray)
        [COLOR="Navy"]If[/COLOR] Ray(J) < Ray(i) [COLOR="Navy"]Then[/COLOR]
            Temp = Ray(i)
            Ray(i) = Ray(J)
            Ray(J) = Temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] J
[COLOR="Navy"]Next[/COLOR] i
Range("J1").Resize(, .Count) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
Click to expand...



This VBA code works for 1 row, how would I adjust the code if I have several rows and want unique sorting on each of the rows
 
Upvote 0
Try This:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG25May11
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Dim[/COLOR] AcRng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, oDn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] oDn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] AcRng = Range(Range("A" & oDn.row), Cells(oDn.row, Columns.Count).End(xlToLeft))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] AcRng
                .Item(Dn.value) = Dn.value
            [COLOR="Navy"]Next[/COLOR]
                Ray = .keys
    [COLOR="Navy"]For[/COLOR] i = 0 To UBound(Ray)
        [COLOR="Navy"]For[/COLOR] J = i To UBound(Ray)
            [COLOR="Navy"]If[/COLOR] Ray(J) < Ray(i) [COLOR="Navy"]Then[/COLOR]
                Temp = Ray(i)
                Ray(i) = Ray(J)
                Ray(J) = Temp
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] J
    [COLOR="Navy"]Next[/COLOR] i
Range("J" & oDn.row).Resize(, .Count) = Ray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] oDn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sort within rows in same column
Replies
2
Views
149
sharshra
S
C
  • Question Question
Vlookup formula that includes searching for specific criteria text within a group of cells
Replies
9
Views
437
AhoyNC
A
S
  • Solved
Sort Option For Filter Function?
Replies
5
Views
405
StephenCrump
S
willow1985
  • Solved
Call a macro based on text in column C (same row that is active).
Replies
8
Views
130
6StringJazzer
6StringJazzer
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
643
ItalianPlatinum
I

Forum statistics

Threads
1,224,536
Messages
6,179,402
Members
452,909
Latest member
VickiS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top