Solved

Excel 2010 Auto sort

Posted on 2011-03-11
8
2,617 Views
Last Modified: 2012-06-27
1 workbook
2 worksheets - Labels and Names

Worksheet2 gets data from worksheet1 thusly:
on Worksheet 2 (Being "Names")   A3 =Labels!A3, A4 =Labels!A4, etc ...
This list on 'Labels' will continue to be added to so I would like to do this open ended if possible, where entering data on the 'Labels' Worksheet auto-populates on the 'Names' worksheet and also automatically alphabetizes, each time they add an entry on 'Labels'

Additional Functionality:
On the 'Names' worksheet each row will have several columns of information associated with it, so I really need it to sort the rows, based on the 1st column, so columns a -  j for example, based on a
0
Comment
Question by:alexianit
  • 4
  • 4
8 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35113843
alexianit, I have a theory aroud it but before I comment may I see a sample file?

Sid
0
 

Author Comment

by:alexianit
ID: 35138554
I've created a dummy file that should help explain the columns and sheets I'm working with.
Book1.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35139928
Ok Upon rereading your post and comparing it with the demo file I see you don't have any data in the Sheet "Names" in column B to J? Could you confirm if there will be any data in those columns?

Sid
0
 

Author Comment

by:alexianit
ID: 35141129
Try this one.
Dining-Room-Labels.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35170828
alexianit: Ok, I saw the file and was able to create a basic sort but here is a quick question. Where are you picking the values of Col B to M in Sheet "Names"?

The logic that I am following if to sort the data in Sheet "Labels" automatically when you enter the data in the Sheet "Labels" This ensures that the list in both the sheets in Col A are in Proper Case and are sorted. The only challenge is the columns in Sheets "Name".

Sid
0
 

Author Comment

by:alexianit
ID: 35207037
information in those columns is entered manually
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35213534
alexianit: Here is an alternative. Please read the below before opening the file. This is the best alternative that I could think of for sorting in your case. Let me know if you have any questions.

When you open the workbook, you will notice that I have entered some sample data in Sheet "Names".

What the code does is

1) The moment you enter any value or make any changes in Col A of Sheet "Labels", it
creates a blank sheet and copies all the data from the "Names" Sheet and pastes it there.
2) It then clears the "Names" Sheet
3) Sorts Col A-D in Sheet "Labels"
4) Copies the sorted values to sheet "Names" and repopulates the respective Col B-M from the temp sheet
5) Deletes the temp sheet.

Please note that I have removed all the formulas from the Sheet "Names" as it is not required any more.

Hope this helps?

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRowAs As Long, lastRow As Long, i As Long
    Dim ASheet As Worksheet, ws As Worksheet, ws1 As Worksheet
    
    Set ASheet = Sheets("Labels")
    Set ws1 = Sheets("Names")
    
    lastRowAs = ASheet.Range("A" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    On Error GoTo Whoa
    
    If Not Intersect(Target, Range("A2:B" & lastRowAs)) Is Nothing Then
        Application.EnableEvents = False
        
        Set ws = Sheets.Add
        ws.Name = "Temp"
        
        lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
        
        ws1.Range("A3:M" & lastRow).Copy
        ws.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        ASheet.Range("A2:D" & lastRowAs).Sort _
        Key1:=ASheet.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
        ws1.Rows("3:" & lastRow).ClearContents
    
        ASheet.Range("A2:A" & lastRowAs).Copy _
        ws1.Range("A3")
        
        For i = 3 To lastRow
            strSearch = ws1.Range("A" & i)
            Set acell = ws.Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not acell Is Nothing Then
                ws.Range("B" & acell.Row & ":M" & acell.Row).Copy
                ws1.Range("B" & i).PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If

        Next i
        
LetsContinue:
        Application.DisplayAlerts = False
        On Error Resume Next
        ws.Delete
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Set ASheet = Nothing
        Set ws = Nothing
        Set ws1 = Nothing
    End If
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

Dining-Room-Labels-1.xls
0
 

Author Closing Comment

by:alexianit
ID: 35393759
Good answer.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

867 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

22 Experts available now in Live!

Get 1:1 Help Now