Solved

Excel 2010 Auto sort

Posted on 2011-03-11
8
2,604 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
Comment Utility
alexianit, I have a theory aroud it but before I comment may I see a sample file?

Sid
0
 

Author Comment

by:alexianit
Comment Utility
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
Comment Utility
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
Comment Utility
Try this one.
Dining-Room-Labels.xls
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
information in those columns is entered manually
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
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
Comment Utility
Good answer.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

14 Experts available now in Live!

Get 1:1 Help Now