Solved

sort excel output into unique rows

Posted on 2011-02-21
5
872 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
0
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
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
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

        .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
0
 

Author Comment

by:stefanjoc
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

by:SiddharthRout
ID: 34951363
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
0
 
LVL 24

Expert Comment

by:broomee9
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question