Solved

advanced sort of excel by the contents of another column

Posted on 2011-02-21
32
384 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:stefanjoc
  • 16
  • 13
  • 3
32 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942500
May I see a sample?

Sid
0
 

Author Comment

by:stefanjoc
ID: 34942518
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942524
Yes this does. Let me create a sample for you.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942562
Please confirm your excel version?

Sid
0
 

Author Comment

by:stefanjoc
ID: 34942566
excel 2003
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34942572
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
 

Author Comment

by:stefanjoc
ID: 34942586
ok cool of course, so we are "tagging" the row for a valid entry, and then sorting on all valid entries?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34942605
Correct. :)
0
 

Author Closing Comment

by:stefanjoc
ID: 34942614
perfect many thanks for your help :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942636
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
 

Author Comment

by:stefanjoc
ID: 34942684
ah thanks alot Sid.  I really appreciate the help :-)
0
 

Author Comment

by:stefanjoc
ID: 34942692
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942711
Here is the sample file. Please run the code "Sample" in the module :)

Sid
Sort-Example.xls
0
 

Author Comment

by:stefanjoc
ID: 34942837
ok ive ran it but cant say I saw anything happen?  what does it do? :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942866
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
 

Author Comment

by:stefanjoc
ID: 34942954
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34942964
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
 

Author Comment

by:stefanjoc
ID: 34942991
oh ok :-)  well its the same as the sample I posted before?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943001
No, give me few minutes. posting a fresh sample.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943022
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943082
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943105
Or if you want to show the data in an arranged format then yes leave the 'sort' code as it is.

Sid
0
 

Author Comment

by:stefanjoc
ID: 34943161
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943175
I wasn't helping you for points ;)

I just wanted you to achieve what you want :)

Looking forward for the next question :)

Sid
0
 

Author Comment

by:stefanjoc
ID: 34943188
;-)  could it be changed to only show unique records?  that would be the icing :-), as in, rather than 6 freds, just one?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943201
Sure. The code is ready. Waiting for you to post the next question :)

Sid
0
 

Author Comment

by:stefanjoc
ID: 34943249
haha you mean a new question on here, or on this thread?  Can you show me how to show only unique records?  :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943260
Not for points but since you have a new question, it has to go into a new thread.

Sid
0
 

Author Comment

by:stefanjoc
ID: 34943274
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943282
You are welcome :)

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34943291
So it is for points. ;)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943306
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Outlook Free & Paid Tools
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now