Home

Help with Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-832579%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832579%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20downloaded%20an%20excel%20spreadsheet%20from%20google%20docs.%3C%2FP%3E%3CP%3EIn%20one%20column%2C%20are%20the%20names%20of%20people%20alphabetized.%26nbsp%3B%20The%20other%20columns%20are%20the%20names%20of%20their%20club%20and%20another%20column%20is%20the%20town%20they%20live%20in.%26nbsp%3B%20How%20do%20I%20set%20up%20the%20spreadsheet%20(now%20saved%20to%20my%20computer)%20so%20that%20if%20I%20click%20on%20any%20of%20these%20columns%2C%20the%20entire%20data%20will%20rearrange%2Falphabetized%20to%20the%20column%20I%20have%20selected%3F%26nbsp%3B%20I%20have%20only%20been%20able%20to%20find%20out%20how%20to%20alphabetize%20a%20column%20but%20then%20just%20that%20column%2C%20(not%20all%20the%20columns)%20rearranges.%26nbsp%3B%20%26nbsp%3BOne%20row%20of%20information%20is%20data%20of%20one%20person%20that%20needs%20to%20stay%20intact.%26nbsp%3B%20Thank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-832579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832625%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401580%22%20target%3D%22_blank%22%3E%40NPRenae%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%3CSTRONG%3EBefore%20applying%20any%20sort%20to%20your%20list%2C%20make%20sure%20of%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EYour%20columns%20have%20headers%3C%2FLI%3E%3CLI%3EYou%20have%20Blanks%20all%20around%20your%20list%3C%2FLI%3E%3CLI%3ENo%20blank%20columns%20in%20between%3C%2FLI%3E%3CLI%3EYou%20select%20one%20SINGLE%20Cell%20in%20the%20column%20that%20will%20be%20the%20primary%20sort.%3C%2FLI%3E%3CLI%3EDo%20not%20select%20the%20Entire%20Column.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EAccordingly%20Excel%20will%20resize%20the%20selection%20and%20sorts%20All%20the%20records%20based%20upon%20the%20column%20you%20specified.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129569iFC90DDED97C17099%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20you%20select%20a%20Single%20cell%20in%20the%20column%20you%20want%20(Name%20or%20Club%20or%20Town)%20%3CSTRONG%3Eyou%20can%20sort%20by%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EClick%20on%20Sort%20%26amp%3B%20Filter%20to%20the%20right%20side%20of%20the%20Home%20Tab%3C%2FLI%3E%3CLI%3ERight%20click%20and%20Sort%3C%2FLI%3E%3CLI%3EClick%20on%20Ascending%20or%20Descending%20on%20the%20Data%20Tab%3C%2FLI%3E%3CLI%3EUse%20the%20Shortcuts%20for%20ascending%20or%20descending%3A%26nbsp%3B%20ALT%2C%20A%20S%20A%26nbsp%3B%20%26nbsp%3Bor%20ALT%2C%20A%20S%20D%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20attached%20a%20Sample%20File%3C%2FP%3E%3CP%3EGood%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832667%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401580%22%20target%3D%22_blank%22%3E%40NPRenae%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20VBA%20to%20automate%20the%20Data%20Sorting%20so%20that%20whenever%20you%20select%20a%20column%20header%2C%20the%20data%20will%20be%20sorted%20in%20Ascending%20order%20in%20the%20column%20of%20the%20selected%20header.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20always%20a%20best%20practice%20to%20add%20an%20Index%20column%20so%20that%20you%20can%20return%20to%20the%20original%20state%20of%20data%20by%20sorting%20the%20Index%20column%20at%20any%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20contains%20a%20Selection%20Change%20Event%20to%20sort%20the%20data%20automatically%20once%20you%20select%20a%20column%20header.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20an%20Index%20column%20which%20can%20be%20sorted%20to%20get%20back%20the%20data%20in%20it's%20original%20state.%20If%20you%20don't%20need%20the%20Index%20column%2C%20you%20can%20delete%20that%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20code%20is%20placed%20on%20the%20Sheet1%20Module.%3C%2FP%3E%3CP%3ETo%20implement%20the%20code%20to%20your%20original%20workbook%2C%20follow%20these%20steps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EOpen%20your%20File.%3C%2FLI%3E%3CLI%3ERight%20click%20on%20Sheet%20Tab%20and%20choose%20View%20Code.%3C%2FLI%3E%3CLI%3EPaste%20the%20code%20given%20below%20into%20the%20opened%20code%20window.%3C%2FLI%3E%3CLI%3EClose%20the%20VB%20editor.%3C%2FLI%3E%3CLI%3ESave%20your%20Workbook%20either%20as%20.xlsm%2C%20.xls%20or%20.xlsb%20i.e.%20a%20file%20format%20which%20supports%20Macros%20in%20it.%3C%2FLI%3E%3C%2FOL%3E%3CP%3ELet%20me%20know%20if%20this%20is%20something%20you%20can%20work%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3ECode%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%0AIf%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0AIf%20Not%20Intersect(Target%2C%20Range(%22A1%22).CurrentRegion.Rows(1))%20Is%20Nothing%20Then%0A%20%20%20%20ActiveSheet.Sort.SortFields.Clear%0A%20%20%20%20Range(%22A1%22).CurrentRegion.Sort%20key1%3A%3DTarget.Offset(1%2C%200)%2C%20order1%3A%3DxlAscending%2C%20Header%3A%3DxlYes%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
NPRenae
Occasional Visitor

Hello,

I have downloaded an excel spreadsheet from google docs.

In one column, are the names of people alphabetized.  The other columns are the names of their club and another column is the town they live in.  How do I set up the spreadsheet (now saved to my computer) so that if I click on any of these columns, the entire data will rearrange/alphabetized to the column I have selected?  I have only been able to find out how to alphabetize a column but then just that column, (not all the columns) rearranges.   One row of information is data of one person that needs to stay intact.  Thank you! 

2 Replies

@NPRenae 

Hi

Before applying any sort to your list, make sure of:

  1. Your columns have headers
  2. You have Blanks all around your list
  3. No blank columns in between
  4. You select one SINGLE Cell in the column that will be the primary sort.
  5. Do not select the Entire Column.

Accordingly Excel will resize the selection and sorts All the records based upon the column you specified.

clipboard_image_0.png

 

When you select a Single cell in the column you want (Name or Club or Town) you can sort by:

  • Click on Sort & Filter to the right side of the Home Tab
  • Right click and Sort
  • Click on Ascending or Descending on the Data Tab
  • Use the Shortcuts for ascending or descending:  ALT, A S A   or ALT, A S D

I attached a Sample File

Good Luck

Nabil Mourad

 

 

@NPRenae 

You may use VBA to automate the Data Sorting so that whenever you select a column header, the data will be sorted in Ascending order in the column of the selected header.

 

It's always a best practice to add an Index column so that you can return to the original state of data by sorting the Index column at any time.

 

The attached file contains a Selection Change Event to sort the data automatically once you select a column header.

 

I have added an Index column which can be sorted to get back the data in it's original state. If you don't need the Index column, you can delete that column.

 

The following code is placed on the Sheet1 Module.

To implement the code to your original workbook, follow these steps...

 

  1. Open your File.
  2. Right click on Sheet Tab and choose View Code.
  3. Paste the code given below into the opened code window.
  4. Close the VB editor.
  5. Save your Workbook either as .xlsm, .xls or .xlsb i.e. a file format which supports Macros in it.

Let me know if this is something you can work with.

 

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("A1").CurrentRegion.Rows(1)) Is Nothing Then
    ActiveSheet.Sort.SortFields.Clear
    Range("A1").CurrentRegion.Sort key1:=Target.Offset(1, 0), order1:=xlAscending, Header:=xlYes
End If
End Sub

 

 

Related Conversations