?
Solved

Excel 2010 Auto sort

Posted on 2011-03-11
8
Medium Priority
?
2,654 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
[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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:alexianit
ID: 35141129
Try this one.
Dining-Room-Labels.xls
0
 
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 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

649 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