advanced sort of excel by the contents of another column

I have 2 pages in excel 2003.  In one page I have headers which have mailbox name, and users who have access.  In the second page I just have mailbox names.  What I want to do is sort the first page so that I can return the rows, only of mailboxes specified in the second page?
stefanjocAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
Add another column to sheet1 with formula:
=ISNUMBER(MATCH(A2,Sheet2!A:A,0))
in row 2 (I assume a header row) and copy down.  Now sort the data on this column first.
0
 
SiddharthRoutCommented:
May I see a sample?

Sid
0
 
stefanjocAuthor Commented:
I cant post the file but for example
1st excel tab
mailbox           username
mailbox1        bob
mailbox1        andy
mailbox1        fred
mailbox6       andy
mailbox6       fred
mailbox9       andy
mailbox9      andy

and on the second tab

mailbox1
mailbox9

So, I want it to return all the rows that match mailbox1 and mailbox9

Does that make sense?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
Yes this does. Let me create a sample for you.

Sid
0
 
SiddharthRoutCommented:
Please confirm your excel version?

Sid
0
 
stefanjocAuthor Commented:
excel 2003
0
 
stefanjocAuthor Commented:
ok cool of course, so we are "tagging" the row for a valid entry, and then sorting on all valid entries?
0
 
Rory ArchibaldCommented:
Correct. :)
0
 
stefanjocAuthor Commented:
perfect many thanks for your help :-)
0
 
SiddharthRoutCommented:
Since I had already worked on this, I might as well update the code. :)

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
        .Columns("B:B").Delete Shift:=xlToLeft
    End With
End Sub

Open in new window


Sid
0
 
stefanjocAuthor Commented:
ah thanks alot Sid.  I really appreciate the help :-)
0
 
stefanjocAuthor Commented:
Im not familiar with code in excel so where would I execute this from?  I'll have a look at it as took the trouble of doing this for me :-)
0
 
SiddharthRoutCommented:
Here is the sample file. Please run the code "Sample" in the module :)

Sid
Sort-Example.xls
0
 
stefanjocAuthor Commented:
ok ive ran it but cant say I saw anything happen?  what does it do? :-)
0
 
SiddharthRoutCommented:
This is what I get.

BEFORE

mailbox      username
mailbox1      bob
mailbox1      andy
mailbox1      fred
mailbox6      andy
mailbox6      fred
mailbox9      andy
mailbox9      andy

AFTER

mailbox      username
mailbox1      bob
mailbox1      andy
mailbox1      fred
mailbox9      andy
mailbox9      andy
mailbox6      andy
mailbox6      fred

Sid
0
 
stefanjocAuthor Commented:
ah I see what you have done now.  What I wanted is for it to only return the rows that have a mailbox name specified on the second sheet.  So given that the second tab only have 1 and 9, it shouldnt display the rows that have mailbox6
0
 
SiddharthRoutCommented:
Oh that can be achieved as well :) Simply use the auto filter in the code to remove the #N/A values. Do you want to give me an example?

Sid
0
 
stefanjocAuthor Commented:
oh ok :-)  well its the same as the sample I posted before?
0
 
SiddharthRoutCommented:
No, give me few minutes. posting a fresh sample.

Sid
0
 
SiddharthRoutCommented:
Here it is. Sample file attached.

Sid

Code Used

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

Open in new window

Sort-Example.xls
0
 
SiddharthRoutCommented:
In fact what I feel is you don't need to sort at all.

Remove line 21 and 22 from the code above :)

Sid

0
 
SiddharthRoutCommented:
Or if you want to show the data in an arranged format then yes leave the 'sort' code as it is.

Sid
0
 
stefanjocAuthor Commented:
what about only showing unique records, so rather than loads of andy and freds?  very good by the way, Im gonna set up another question so you can answer this for the points :-)
0
 
SiddharthRoutCommented:
I wasn't helping you for points ;)

I just wanted you to achieve what you want :)

Looking forward for the next question :)

Sid
0
 
stefanjocAuthor Commented:
;-)  could it be changed to only show unique records?  that would be the icing :-), as in, rather than 6 freds, just one?
0
 
SiddharthRoutCommented:
Sure. The code is ready. Waiting for you to post the next question :)

Sid
0
 
stefanjocAuthor Commented:
haha you mean a new question on here, or on this thread?  Can you show me how to show only unique records?  :-)
0
 
SiddharthRoutCommented:
Not for points but since you have a new question, it has to go into a new thread.

Sid
0
 
stefanjocAuthor Commented:
ok no prob, I'll post one up a little later once I leave, just about to go into a meeting.  Many thanks for your help Sid
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
 
Rory ArchibaldCommented:
So it is for points. ;)
0
 
SiddharthRoutCommented:
No. The OP may create a new question and set the points as 0 and I will still help him ;)

I am just following the trend. New question, New Thread...

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.