1

input-output

I have 350 columns. Below is a subset of all the data.

Sample data in an Excel spreadsheet divided into rows for year and month and columned by district.

I used "Sort Smallest to Largest" tool. It gives results only for one column. If not, it can sort the first column and other columns will change according to the first one. But I want to sort all columns A-Z separately.

I want to sort the data by district.

7
  • I would like to suggest, share some sample data and expected output, will help us to fix the issue! Mar 28, 2019 at 5:46
  • Thank you for your attention. first i am sorry for the spelling mistake of sort. i have added screenshot of the data set.
    – Samith
    Mar 28, 2019 at 6:13
  • Have you tried using a pivot table? A pivot table can summarize the data nicely, but you need to give us an example of what you want the data to look like before we can give you a good answer to your question. Mar 28, 2019 at 6:38
  • can sort first column and other columns will change according to first column. but i want to sort all columns together. that mean every column should sort A-Z together. Does this mean that the result you want is each column to be sorted? ever if some values which were posessed in different columns and in the same row in starting state may occur in different rows in final state? if so it is NOT "together", it is backward "separately".
    – Akina
    Mar 28, 2019 at 7:07
  • @Akina you are correct. i want each column to be sorted
    – Samith
    Mar 28, 2019 at 7:58

1 Answer 1

3

1. Select the column you want to sort. Then click on "Sort Smallest to Largest".

Data > Sort & Filter > Sort Smallest to Largest

2. Choose "Continue with the current selection".

Continue with the current selection

You can see, only one column was sorted.
Sorted only one column

Repeat this for the rest 349 columns :)


In order to do it alltogether in one go:

  1. Open the sheet you want to sort.

  2. Open VBA editor using Alt+F11

  3. Insert > Module

  4. Paste this code:

Sub sort_columns_separately_AZ()
  Dim rngStart As Range
  Dim rngHeaders As Range
  Dim c As Range
  Set rngStart = ActiveSheet.Range("A2")
  Set rngHeaders = ActiveSheet.Range(rngStart, rngStart.End(xlToRight))

  For Each c In rngHeaders
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=c, _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
      .SetRange ActiveSheet.Range(c, c.End(xlDown))
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  Next
End Sub
  1. In the code, change ActiveSheet.Range("A2") with the cell address where your data table starts, for example ActiveSheet.Range("B5") .
  2. Hit F5

Results:
before sorting and after

Note: if you have any empty cells in your data, this code will need adjustments in order to work correctly on those columns which have empty cells.

5
  • wow. superb. thank you soooo much. it's amazing.
    – Samith
    Mar 28, 2019 at 9:37
  • If i want to get Z-A. where i should change it.
    – Samith
    Mar 28, 2019 at 9:38
  • Done it by using Sort and filter tool. But can you tell me how i can do it by VB script.
    – Samith
    Mar 28, 2019 at 9:40
  • If you want Z-A (technically it is called descending), you would need to change Order:=xlAscending to Order:=xlDescending .
    – ZygD
    Mar 28, 2019 at 9:43
  • 1
    It seems it worked out. If so, please check the ✓ symbol next to the answer, marking the question as answered.
    – ZygD
    Mar 28, 2019 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.