Sorting data based on column value

[exceluser]

Hi

Can any one help me with a vba macro code to sort the data based on the column value automatically.

if I have :
Column Name SORT
A 1
B 2
C 3
D 4

 

[Luke M]

Welcome to the forum!

I'd recommend starting by just recording a macro of you sorting the data yourself. That will give you the basic line of code that you need. If you still get stuck, post what the code looks like on this thread, and we can help you tweak it.

 

[Excelnoub]

So you need only Column B to sort?

 

[Bomino]

Probably the following post will help:

http://www.clearlyandsimply.com/cle...ort-excel-tables-by-double-clicking.html#more

 

[Excelnoub]

Untested:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
        With ActiveWorkbook.Worksheets("Sheet1")
            .Range("A2:B1000").Sort Key1:=.Range("B2")
        End With
    End If
Application.ScreenUpdating = True
End Sub

 

[exceluser]

Untested:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
        With ActiveWorkbook.Worksheets("Sheet1")
            .Range("A2:B1000").Sort Key1:=.Range("B2")
        End With
    End If
Application.ScreenUpdating = True
End Sub

Thanks for your replies,

yes something similar it needs to search the value in Column and when that value found assign a number to it in Sort column

if it finds A then put 1 into sort column and if it finds B put 2 into sort column

 

[exceluser]

Thanks for your replies,

yes something similar it needs to search the value in Column and when that value found assign a number to it in Sort column

if it finds A then put 1 into sort column and if it finds B put 2 into sort column

Any one guys based upon my comments?

 

[Debraj]

Can you please upload a sample file.. with expected output..
With this small data view, its really hard to decide

 

[exceluser]

Can you please upload a sample file.. with expected output..
With this small data view, its really hard to decide

Hi Debraj,

There are two columns
Example Type and Sort Order

in which I have put the values in sort order manually, but I need them to change automatically base on the values in Example Type columns.

if example type column shows A then sort order solumn should put the value in sort order column as 1

Thanks

 

[Luke M]

This looks less like an actual sorting problem them, and more like a lookup type problem. You want to give a list of values like A-E, and get an output of 1-5? This formula could do it nicely:
=CODE(UPPER(A2))-64

 

[exceluser]

This looks less like an actual sorting problem them, and more like a lookup type problem. You want to give a list of values like A-E, and get an output of 1-5? This formula could do it nicely:
=CODE(UPPER(A2))-64

Hi Luke,

I want to give only value A and in sort column the code should automatically put the value as 1 and in case its B it should put the value of 2 in the sort column automatically

Thanks

 

[Somendra Misra]

Hi exceluser,

If your problem, as indicated by @Luke M , is to get a value based on A-E, say 1 for A, 2 for B and so on, than Luke formula is working fine. You need to drag the formula in Column B till your requirement and as soon as Column A will receive an alphabet it will display the sort order code.

May be the formula should be surrounded by an IFERROR to display a blank if column A is blank, something like below.

=IFERROR(CODE(UPPER((A3)))-64,"")

Regards,

 

[exceluser]

Hi exceluser,

If your problem, as indicated by @Luke M , is to get a value based on A-E, say 1 for A, 2 for B and so on, than Luke formula is working fine. You need to drag the formula in Column B till your requirement and as soon as Column A will receive an alphabet it will display the sort order code.

May be the formula should be surrounded by an IFERROR to display a blank if column A is blank, something like below.

=IFERROR(CODE(UPPER((A3)))-64,"")

Regards,

Hi Somendra,

Thanks thats working fine ,but will it work for the below scenario

if I have sort column in AD or AE instead of B and in first column I have some word like Sit, Jump, Climb, Fall

 

[Somendra Misra]

@exceluser

The formula given by @Luke M was based on the data your provided. It will not work for the words that you gave now. I suppose here column is not a problem. What will be the sort order for these words? Is there any logic behind giving sort order?

Kindly, explain your requirement in more details.

Regards,

 

[exceluser]

@exceluser

The formula given by @Luke M was based on the data your provided. It will not work for the words that you gave now. I suppose here column is not a problem. What will be the sort order for these words? Is there any logic behind giving sort order?

Kindly, explain your requirement in more details.

Regards,

Hi Somendra,

I have attached the example sheet in which I have provided the details in column A and sort order in Column AF so the search is based on column A values and sort order needs to be automatically added base on value in column A

 

[Somendra Misra]

@exceluser

What will be the sort order for these words? Is there any logic behind giving sort order?

You did not explained this part. If there is no logic and just the words are assigned arbitary these values say 1 for sit , 2 for jump and so on, and it will remain the same order, you can create a master table with all the words and sort order assigned to them. Than you can use VLOOKUP formula to get sort order automatically in column AF.

Regards,

 

[exceluser]

@exceluser



You did not explained this part. If there is no logic and just the words are assigned arbitary these values say 1 for sit , 2 for jump and so on, and it will remain the same order, you can create a master table with all the words and sort order assigned to them. Than you can use VLOOKUP formula to get sort order automatically in column AF.

Regards,

Sort order is fixed for these

SIT 1
JUMP 2
CLIMB 3
FALL 4

 

[Somendra Misra]

@exceluser

See your file.

Regards,

 

[exceluser]

@exceluser

See your file.

Regards,

Thanks Somendra

 

[Luke M]

Alternative to Somendra's idea:
If you have the list already setup in order somewhere else, you can just use MATCH:
=MATCH(A3,OtherList,0)

Thinking ahead, you might also take a look at setting up a custom list, if you need to do a lot of sorting/rearranging based on this order.