Sorting Question

Posted by Jerry on November 12, 2001 11:05 AM

Given Two Columns

A B
A1 B2
B2 A2

A4 B2
C3 C2

Some rows contain blanks, some rows contain duplicate entries. What I want to do is create a single column list of all the data in the two columns less the blanks and duplicate entries, sorted in order. Here's a little wrinkle, if it is possible, I want them to be sorted starting with the last letter/number in the cell.

(In other words, if a cell contains the data GI 5-B3, I want it to look at it like so for sorting purposes: 3B-5 IG)

Thanks for your help!

Posted by Bertie Bagshot on November 12, 2001 3:16 PM

To delete the blanks, select column A, go to Edit>GoTo>Special>Blanks>OK, then Edit>Delete>ShiftCellsUp.
Do the same for column B.

To delete the duplicates, for each column use AdvancedFilter>UniqueRecordsOnly>CopyToAnotherLocation.

To sort in reverse order, select column A (the new location) and run the macro below. This inserts a column between columns A & B and fills it with the column A data in reverse order. Sort the column A data by the inserted column sequence. Delete the inserted column.
Do the same for column B.

Sub ReverseOrder()
Dim cell As Range, c As Integer, x As Integer, rev As String
Application.ScreenUpdating = False
Selection.Offset(0, 1).EntireColumn.Insert
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
c = Len(cell)
rev = ""
For x = c To 1 Step -1
rev = rev & Mid(cell, x, 1)
cell.Offset(0, 1).Value = rev
Next
Next
End Sub

Posted by Bertie Bagshot on November 12, 2001 3:19 PM

Amendment ....

Have just noticed that you want to combine columns A & B into one column.
Before doing any of the above, first select the column B data, cut, and paste underneath the column A data.