Sorting and Aligning Rows

V

VBAEnjoi

New Member
Joined
Sep 30, 2018
Messages
33
I have data in n number of rows and 2 columns.
I want to sort data in rows so that the matching values of 2 columns are aligned in the same row.
The Original data set and Required outcome below:
Original Data set
[TABLE="width: 230"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple2Red[/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Apple1Red[/TD]
[TD]Orange5Red[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Apple1 Red[/TD]
[TD]Orange1Red[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Apple1Yellow[/TD]
[TD]Apple1Yellow[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Orange1Black[/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Orange1White[/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Orange5Red[/TD]
[TD]Orange1White[/TD]
[/TR]
</tbody>[/TABLE]

Required outcome

[TABLE="width: 211"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Apple1 Red[/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD]Apple1Red[/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple1Red[/TD]
[/TR]
[TR]
[TD]Apple1Yellow[/TD]
[TD]Apple1Yellow[/TD]
[/TR]
[TR]
[TD]Apple2Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange1Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange1White[/TD]
[TD]Orange1White[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange1Red[/TD]
[/TR]
[TR]
[TD]Orange5Red[/TD]
[TD]Orange5Red[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Your test data is treating text with space the same as text without space
(The second Apple1 Red in column A contains a space but is treated as if it does not in your example)
- so the VBA below includes a step to remove spaces in order to return required result
- if that is not what you want, then remove that step

Your sample data
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Apple2Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Apple1Red[/td][td]Orange5Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Apple1 Red[/td][td]Orange1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Orange1Black[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Orange1White[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Orange5Red[/td][td]Orange1White[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Sheet copied to temporary sheet and ALL spaces removed
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Apple2Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Apple1Red[/td][td]Orange5Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Apple1Red[/td][td]Orange1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Orange1Black[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Orange1White[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Orange5Red[/td][td]Orange1White[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1 (2)[/td][/tr][/table]

ALL data (from both columns) is copied to column A and duplicated in column B
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Apple2Red[/td][td]Apple2Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Orange1Black[/td][td]Orange1Black[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Orange1Red[/td][td]Orange1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Orange1White[/td][td]Orange1White[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Orange1White[/td][td]Orange1White[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Orange5Red[/td][td]Orange5Red[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: DataDuplicated[/td][/tr][/table]

Unwanted values removed from each column
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Apple2Red[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Orange1Black[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td]Orange1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Orange1White[/td][td]Orange1White[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Orange5Red[/td][td]Orange5Red[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: ValuesCleared[/td][/tr][/table]

Resultant empty rows deleted
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Apple1Red[/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td]Apple1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Apple1Yellow[/td][td]Apple1Yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Apple2Red[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Orange1Black[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td]Orange1Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Orange1White[/td][td]Orange1White[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Orange5Red[/td][td]Orange5Red[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Result[/td][/tr][/table]

Code: 
Sub SortData()
    Dim ws As Worksheet, sh As Worksheet
    Dim cel As Range, celA As Range, celB As Range, rngA As Range, rngB As Range, rngA2 As Range, rngB2 As Range, Del As Range
    Dim r As Long, txt As String
    
[I][COLOR=#006400]'add sheet and remove all spaces from text[/COLOR][/I]
    Sheets("Sheet1").Copy Before:=Sheets(1)
    Set sh = Sheets(1)
    Set rngA = sh.Range("A1").CurrentRegion.Resize(, 1)
    Set rngB = rngA.Offset(, 1)
    For Each cel In Union(rngA, rngB)
        cel = Replace(cel, " ", "")
    Next

[COLOR=#006400][I]'add sheet, copy all data (both columns A&B) to column A and sort[/I][/COLOR]
    Set ws = Sheets.Add
    rngA.Copy ws.Cells(1, 1)
    rngB.Copy ws.Cells(ws.Rows.Count, 1).End(xlUp)
    ws.Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
[COLOR=#006400][I]'copy columnA to column B and remove items not required from each column[/I][/COLOR]
    ws.Range("A:A").Copy ws.Cells(1, 2)
    Set Del = ws.Cells(ws.Rows.Count, 1)
    For r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row To 1 Step -1
        Set celA = ws.Cells(r, 1)
        Set celB = celA.Offset(, 1)
        Set rngA2 = ws.Cells(1, 1).Resize(r)
        Set rngB2 = rngA2.Offset(, 1)
        txt = celA
        If Occurs(rngA2, txt) > Occurs(rngA, txt) Then celA.ClearContents
        If Occurs(rngB2, txt) > Occurs(rngB, txt) Then celB.ClearContents
        If Len(celA) + Len(celB) = 0 Then Set Del = Union(Del, celA)
        txt = ""
    Next r
[COLOR=#006400][I]'delete blank rows and temporary sheet[/I][/COLOR]
    Del.EntireRow.Delete
    Application.DisplayAlerts = False
        sh.Delete
    Application.DisplayAlerts = True
End Sub

Private Function Occurs(xRange As Range, xText As String) As Long
    Occurs = WorksheetFunction.CountIf(xRange, xText)
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sort within rows in same column
Replies
2
Views
130
sharshra
S
D
  • Question Question
Remove matching rows with pause function
Replies
1
Views
177
alansidman
A
H
  • Question Question
Match and sort different sized data sets
Replies
8
Views
725
Hammer123
H
I
  • Solved
Irregular Data Set
2
Replies
19
Views
320
IdabaMalouki
I
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
340
STARL0RD
S

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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