Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

advanced sort of excel by the contents of another column

Posted on 2011-02-21
32
Medium Priority
?
398 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 2000 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
 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

886 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