Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

sort excel output into unique rows

Posted on 2011-02-21

1 solution

Medium Priority

892 Views

Last Modified: 2012-05-11

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

.Columns("A:C").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
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

Comment

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
Learn & ask questions

Join The Community

5 Comments

LVL 30

Accepted Solution

by:SiddharthRout

SiddharthRout earned 2000 total points

ID: 349435352011-02-21

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

        .Columns("A:C").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
        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

Open in new window

Sort-Example.xls

Author Comment

by:stefanjoc

ID: 349501972011-02-22

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

LVL 30

Expert Comment

by:SiddharthRout

ID: 349513632011-02-22

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

Please upload your excel file with a sample data so that I can test it.

Sid

LVL 24

Expert Comment

by:Tracy

ID: 352253392011-03-27

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.

Featured Post

Question has a verified solution.

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

Freeze Panes feature

Article by: Rob

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

Microsoft Excel

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

Creating Interactive Pivot Charts in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Microsoft Excel

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…