Solved

Excel 2010 Auto sort

Posted on 2011-03-11
8
2,632 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

790 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