Solved

# sort excel output into unique rows

Posted on 2011-02-21
878 Views
Hi

Sid on this site kindly posted the following code to sort excel data based on the content of rows from another tab in excel.  I would like to do is for it to then sort the output into unique rows so that I can show only the unique records of one of the output columns.  the code is as follows.

Sub Sample()
Dim ws1 As Worksheet
Dim ws1LastRow As Long

Set ws1 = Sheets("Sheet1")

With ws1
ws1LastRow = .Range("A" & Rows.Count).End(xlUp).Row

.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1],1,0)"
.Range("B2").AutoFill Destination:=Range("B2:B" & ws1LastRow)
.Range("B2:B8").Copy
.Range("B2:B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("B1").FormulaR1C1 = "Temp"
.Range("A1:C8").Select

Application.CutCopyMode = False

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Range("A1:C1").AutoFilter
.Range("\$A\$1:\$C\$" & ws1LastRow).AutoFilter Field:=2, Criteria1:="#N/A"
.Range("\$A\$1:\$C\$" & ws1LastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns("B:B").Delete Shift:=xlToLeft
.Range("A1:C1").AutoFilter
End With
End Sub
0
Question by:stefanjoc
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 30

Accepted Solution

SiddharthRout earned 500 total points
ID: 34943535
Try this. Sample File Attached.

``````Sub Sample()
Dim ws1 As Worksheet
Dim ws1LastRow As Long, x As Long

Set ws1 = Sheets("Sheet1")

With ws1
ws1LastRow = .Range("A" & Rows.Count).End(xlUp).Row

.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-1],1,0)"
.Range("B2").AutoFill Destination:=Range("B2:B" & ws1LastRow)
.Range("B2:B8").Copy
.Range("B2:B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("B1").FormulaR1C1 = "Temp"
.Range("A1:C8").Select

Application.CutCopyMode = False

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Range("A1:C1").AutoFilter
.Range("\$A\$1:\$C\$" & ws1LastRow).AutoFilter Field:=2, Criteria1:="#N/A"
.Range("\$A\$1:\$C\$" & ws1LastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns("B:B").Delete Shift:=xlToLeft
.Range("A1:C1").AutoFilter

ws1LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For x = ws1LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(.Range("A1:A" & x), .Range("A" & x).Text) > 1 Then
.Range("A" & x).EntireRow.Delete
End If
Next x
End With
End Sub
``````
Sort-Example.xls
0

Author Comment

ID: 34950197
Hi ya.  Sorry haveny got back to you, rushed afternoon.  I have had a look at the bit that removes the duplicates and its removing everything but one.  What I meant was to sort the colum so that it only shows one entry for each person.  so, andy and frank may have access to more than one mailbox, however after the sort I dont want two franks in the list, just one?  Do you see what I mean?

mailbox9      andy
mailbox9      frank
mailbox9      bob
mailbox6      andy
mailbox6      bob
mailbox6      andy
mailbox6      Frank
mailbox6      Frank
mailbox1      bob
mailbox1      andy
mailbox5      Sid
mailbox5      frank
0

LVL 30

Expert Comment

ID: 34951363
I just tested it. It is working just fine :)

Sid
0

LVL 24

Expert Comment

ID: 35225339
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month4 days, 12 hours left to enroll