Sort Data from Largest to smallest

[bassemjohn1]

Hello All

so I need to sort my numbers in column K from Largest to smallest, the problem is I have another header in row 13 and it changes every month, what is the right VBA code to filter just the numbers and sort it from largest to smallest without messing with my headers in row 1 and 13.

1542001 - 2364001 Recon June- final 5.XLSX
	K			N
1	Amount in local currency			Eff.exchange rate
2	-321.874			0.37706
3	-1,630.111			0.37701
4	-524.125			0.37701
5	-2,742.819			0.37700
6	-12,430.379			0.37700
7	-396.447			0.37700
8	672.448			1.00000
9
10
11
12
13	Amount in local currency			Eff.exchange rate
14	1,630.111			0.37701
15	321.874			1.00000
16	524.189			0.37706
17	2,742.819			0.37700
18	12,442.980			0.37738
19	396.513			0.37706
BHR - BHC

 

[SanjayGMusafir]

Convert data from K1:N8 into a structured table and do whatever you want to do. That shall only affect your table data and nothing else.

 

[Peter_SSs]

What other columns, if any, need to get sorted along with column K?

Are you needing to sort each of those sections in column K or only that first one in rows 2 to 8?

 

[bassemjohn1]

What other columns, if any, need to get sorted along with column K?

Are you needing to sort each of those sections in column K or only that first one in rows 2 to 8?

Hey

so i want to sort the numbers from K2 to K19 ((it changes every month) from Largest to smallest, problem is i have another header in row 13 that mess up the sorting and i need that header in row 13.

1542001 - 2364001 Recon June- final 5.XLSX
	K			N
1	Amount in local currency			Eff.exchange rate
2	-12,430.379			0.37700
3	-2,742.819			0.37700
4	-1,630.111			0.37701
5	-524.125			0.37701
6	-396.447			0.37700
7	-321.874			0.37706
8	321.874			1.00000
9	396.513			0.37706
10	524.189			0.37706
11	672.448			1.00000
12	1,630.111			0.37701
13	2,742.819			0.37700
14	12,442.980			0.37738
15	Amount in local currency			Eff.exchange rate
16
17
18
19
20
BHR - BHC

As you can see, after sorting it moved the header from row 13 to row 15, i want my header to stay without movement but only the data within to be sorted

 

[Peter_SSs]

I'm afraid that didn't answer my questions.

It looks like after you have sorted, column N has also changed order. My first question was what other columns need to get sorted with column K. It looks like column N is one but what about others?

My other question was about whether only rows 2:8 get sorted or whether another similar sort is required for rows 14:19 (& possibly more groups of rows below)?
As well as answering the first question above, perhaps you could post what results you do want, rather than posting results that you don't want?

so i want to sort the numbers from K2 to K19 ((it changes every month) from Largest to smallest,

Yet in your sorted sample above, they are sorted from smallest to largest.
Which is it to be?

 

[bassemjohn1]

I'm afraid that didn't answer my questions.

It looks like after you have sorted, column N has also changed order. My first question was what other columns need to get sorted with column K. It looks like column N is one but what about others?

My other question was about whether only rows 2:8 get sorted or whether another similar sort is required for rows 14:19 (& possibly more groups of rows below)?
As well as answering the first question above, perhaps you could post what results you do want, rather than posting results that you don't want?


Yet in your sorted sample above, they are sorted from smallest to largest.
Which is it to be?

yes there are other columns and they all change when I sort column K (it's sensitive info so I didn't share the whole sheet)

Exactly, Rows 2:8 and Rows 14:19 needs to be sorted as any amounts appear in column K needs to be sorted

The results I want is to sort amounts in column K Rows 2:8 then go sort amounts in rows 14:19, but treat them as 2 separate tables in the same sheet with 2 headers instead of 1 header and 1 table.

I want from largest to smallest or vice versa doesn't really matter.

I'm sorry for the confusion and I really appreciate your time replying to this.

 

[kvsrinivasamurthy]

Code for macro.

Sub SortWithHeader()
Dim Lr As Long, X As Long, Z As Long, Y As Long
Dim A, C, K
Dim T As Long

Lr = Range("K" & Rows.Count).End(xlUp).Row
A = Range("K1:N" & Lr)
ReDim C(1 To Lr, 1 To 4)

With CreateObject("Scripting.dictionary")
For T = 2 To Lr
If A(T, 1) <> "" Then
.Add A(T, 1), T
If A(T, 1) = "Amount in local currency" Then Z = T
End If
Next T
For X = 1 To .Count
Y = Y + 1
    If Y = Z Then
    C(Y, 1) = A(X, 1): C(Y, 2) = A(X, 2): C(Y, 3) = A(X, 3): C(Y, 4) = A(X, 4)
    X = X - 1
    Else
    K = .Item(Application.Small(.keys, X))
    If K = Empty Then Exit For
    C(Y, 1) = A(K, 1): C(Y, 2) = A(K, 2): C(Y, 3) = A(K, 3): C(Y, 4) = A(K, 4)
    End If
'End If
Next X
Range("K2:N" & Lr) = C
End With
End Sub

Result of Macro.

 

[bassemjohn1]

Code for macro.

Sub SortWithHeader()
Dim Lr As Long, X As Long, Z As Long, Y As Long
Dim A, C, K
Dim T As Long

Lr = Range("K" & Rows.Count).End(xlUp).Row
A = Range("K1:N" & Lr)
ReDim C(1 To Lr, 1 To 4)

With CreateObject("Scripting.dictionary")
For T = 2 To Lr
If A(T, 1) <> "" Then
.Add A(T, 1), T
If A(T, 1) = "Amount in local currency" Then Z = T
End If
Next T
For X = 1 To .Count
Y = Y + 1
    If Y = Z Then
    C(Y, 1) = A(X, 1): C(Y, 2) = A(X, 2): C(Y, 3) = A(X, 3): C(Y, 4) = A(X, 4)
    X = X - 1
    Else
    K = .Item(Application.Small(.keys, X))
    If K = Empty Then Exit For
    C(Y, 1) = A(K, 1): C(Y, 2) = A(K, 2): C(Y, 3) = A(K, 3): C(Y, 4) = A(K, 4)
    End If
'End If
Next X
Range("K2:N" & Lr) = C
End With
End Sub

Result of Macro.
View attachment 72198

Thanks for your effort!

Is there a way to treat them as 2 separate tables and not mix the amounts As amounts in Row 2:8 are in a table with a header, Amounts in Row 14:19 are in a different table with their own header
both are in the same sheet but I want to treat each table on its own and not mix the amounts so I need to sort amounts in the first table alone, then sort the amounts on the second table alone and not mix them.

 

[Peter_SSs]

yes there are other columns and they all change when I sort column K (it's sensitive info so I didn't share the whole sheet)

You don't have to show me the data, but please tell me which columns need to be included in the sort. Is it columns A:N or is it columns D:AZ or something else?

 

[bassemjohn1]

it's From Column A:Z

You don't have to show me the data, but please tell me which columns need to be included in the sort. Is it columns A:N or is it columns D:AZ or something else?