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

 

[Peter_SSs]

it's From Column A:Z

Thank you!

Try this with a copy of your data.

Sub SortGroupRows()
  Dim rA As Range
  
  For Each rA In Columns("K").SpecialCells(xlConstants, xlNumbers).Areas
    Intersect(rA.EntireRow, Columns("A:Z")).Sort Key1:=rA, Order1:=xlAscending, Header:=xlNo
  Next rA
End Sub

 

[bassemjohn1]

Thank you!

Try this with a copy of your data.

Sub SortGroupRows()
  Dim rA As Range
 
  For Each rA In Columns("K").SpecialCells(xlConstants, xlNumbers).Areas
    Intersect(rA.EntireRow, Columns("A:Z")).Sort Key1:=rA, Order1:=xlAscending, Header:=xlNo
  Next rA
End Sub

Thank you sir !!! worked like a charm

 

[bassemjohn1]

May I ask you another question please, I'm reconciling debit and credit amounts and it's matching perfectly using my VBA, except some amounts have minor changes in the decimals so the formula can't match them as they are not identical in amount

For EX:

1542001 - 2364001 Recon June- final 5.XLSX
	K	L
1	Amount in local currency	Recon.
2	-321.874	321.874-1
3	-1,630.111	1630.111-1
4	-524.125	524.125-1
5	-2,742.819	2742.819-1
6	-12,430.379	12430.379-1
7	-396.447	396.447-1
8	672.448	672.448-1
9
10
11
12
13	Amount in local currency	Amount in local currency-1
14	1,630.111	1630.111-1
15	321.874	321.874-1
16	524.189	524.189-1
17	2,742.819	2742.819-1
18	12,442.980	12442.98-1
19	396.513	396.513-1
BHR - BHC

Cell Formulas
Range	Formula
L13:L19,L2:L8	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))

As you can see the amounts in yellow are matching but there is a minor difference in the decimals that prevents them from matching, is there a tweak in my formula so I can make these amounts with minor decimal differences match also?

 

[bassemjohn1]

My Formulas are in column L and M

1542001 - 2364001 Recon June- final 5.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-321.874	321.874-1	x
3	-1,630.111	1630.111-1	x
4	-524.125	524.125-1
5	-2,742.819	2742.819-1	x
BHR - BHC

Cell Formulas
Range	Formula
L2:L5	L2	=IF(K2<0,-K2&"-"&COUNTIF(K$2:K2,K2),K2&"-"&COUNTIF(K$2:K2,K2))
M2:M5	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

 

[Peter_SSs]

Thank you sir !!! worked like a charm

You're welcome.
Just needed answers to those questions way back at post #3.

As you can see the amounts in yellow are matching but there is a minor difference in the decimals that prevents them from matching, is there a tweak in my formula so I can make these amounts with minor decimal differences match also?

How do you define what is a "minor difference" and what is a "major difference"?

Also, are you looking for a change to the column L formula or the column M formula or both?

Also, your current column L formula could be simplified to

=ABS(K2)&"-"&COUNTIF(K$2:K2,K2)

BTW, no need to Quote something every time you post - only if it is relevant to the current post or indicating who you are replying to. I have removed some of your excess quotes as it makes your posts and the whole thread easier to read & follow.

 

[bassemjohn1]

How do you define what is a "minor difference" and what is a "major difference"?

Also, are you looking for a change to the column L formula or the column M formula or both?

Also, your current column L formula could be simplified to

=ABS(K2)&"-"&COUNTIF(K$2:K2,K2)

By difference i mean: any difference in decimals as i don’t mind if there is a difference in decimals between my debits and credits

I am looking to change both column L and M formulas

Thank you for quick responses.

 

[Peter_SSs]

By difference i mean: any difference in decimals as i don’t mind if there is a difference in decimals between my debits and credits

Is this what you mean then?
Change to column L formula only.

bassemjohn1.xlsm
	K	L	M
1	Amount in local currency
2	-12430.379	12430-1
3	-2742.819	2743-1	x
4	-1630.111	1630-1	x
5	-524.125	524-1	x
6	-396.447	396-1
7	-321.874	322-1	x
8	672.448	672-1
9
10
11
12
13	Amount in local currency
14	321.874	322-1	x
15	396.513	397-1
16	524.189	524-1	x
17	1630.111	1630-1	x
18	2742.819	2743-1	x
19	12442.98	12443-1
Sheet1 (4)

Cell Formulas
Range	Formula
L2:L8,L14:L19	L2	=ROUND(ABS(K2),0)&"-"&COUNTIF(K$2:K2,K2)
M2:M8,M14:M19	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

 

[kvsrinivasamurthy]

Upload how your result should be.

 

[bassemjohn1]

Is this what you mean then?
Change to column L formula only.

bassemjohn1.xlsm
	K	L	M
1	Amount in local currency
2	-12430.379	12430-1
3	-2742.819	2743-1	x
4	-1630.111	1630-1	x
5	-524.125	524-1	x
6	-396.447	396-1
7	-321.874	322-1	x
8	672.448	672-1
9
10
11
12
13	Amount in local currency
14	321.874	322-1	x
15	396.513	397-1
16	524.189	524-1	x
17	1630.111	1630-1	x
18	2742.819	2743-1	x
19	12442.98	12443-1
Sheet1 (4)

Cell Formulas
Range	Formula
L2:L8,L14:L19	L2	=ROUND(ABS(K2),0)&"-"&COUNTIF(K$2:K2,K2)
M2:M8,M14:M19	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

i tried to add the new formulas to my VBA but an error of mismatch occurs

Range("L2").Formula = "=ROUND(ABS(K2),0)&" - "&COUNTIF(K$2:K2,K2)"
 Range("M2").Formula = "=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")"

 

[Peter_SSs]

If you are inserting formulas with vba then you have to double-up the quote marks within the formula.

Range("L2").Formula = "=ROUND(ABS(K2),0)&"" - ""&COUNTIF(K$2:K2,K2)"
Range("M2").Formula = "=IF(COUNTIF($L$2:$L$292,L2)=2,""x"","""")"