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

 

[bassemjohn1]

In column L, I think you might need separate formulas for the top section and bottom section.

bassemjohn1.xlsm
	K	L	M
1	Amount in local currency	Recon.	Match
2	-51.02	51 - 1	x
3	-2092.372	2092 - 1	x
4	-149.34	149 - 1	x
5	-8373	8373 - 1
6	-12.455	12 - 1
7	-1677.06	1677 - 1	x
8	-121.843	121 - 1	x
9	-345.16	345 - 1
10	-2978.755	2978 - 1	x
11	-149.46	149 - 2	x
12	-703.852	703 - 1	x
13	-627.188	627 - 1	x
14	-3216.448	3216 - 1	x
15	-3517.546	3517 - 1	x
16	-1241.313	1241 - 1	x
17	-117.32	117 - 1	x
18	-77.249	77 - 1	x
19	-965.334	965 - 1	x
20	-834.041	834 - 1	x
21	-77.248	77 - 2	x
22	-37.698	37 - 1	x
23
24
25
26
27	Amount in local currency
28	51.02	51 - 1	x
29	2092.372	2092 - 1	x
30	149.34	149 - 1	x
31	121.84	121 - 1	x
32	2978.755	2978 - 1	x
33	149.46	149 - 2	x
34	703.852	703 - 1	x
35	627.188	627 - 1	x
36	8385.455	8385 - 1
37	3216.448	3216 - 1	x
38	3517.546	3517 - 1	x
39	1241.313	1241 - 1	x
40	1677.06	1677 - 1	x
41	117.32	117 - 1	x
42	77.249	77 - 1	x
43	965.334	965 - 1	x
44	834.041	834 - 1	x
45	77.249	77 - 2	x
46	37.698	37 - 1	x
Sheet3

Cell Formulas
Range	Formula
L2:L22	L2	=INT(ABS(K2))&" - "&COUNTIF(L$1:L1,INT(ABS(K2))&" -*")+1
M2:M46	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")
L28:L46	L28	=INT(K28)&" - "&COUNTIF(L$27:L27,INT(K28)&" -*")+1

Worked perfectly, the only problem is the report changes monthly and it won't be L28 every time. the range changes.

 

[Peter_SSs]

If you are still using a macro to sort the two sections then the macro could work out the ranges and insert the relevant formulas.

 

[bassemjohn1]

If you are still using a macro to sort the two sections then the macro could work out the ranges and insert the relevant formulas.

Would you please explain how?
if am using a macro and stating in my L formula that INT=(K28) then the macro would catch K28 every time.

 

[Peter_SSs]

Would you please explain how?

Try this with a copy of your data.

Sub SortPlusFormulas()
  Dim rng1 As Range, rng2 As Range
  
  Set rng1 = Columns("K").SpecialCells(xlConstants, xlNumbers).Areas(1)
  Set rng2 = Columns("K").SpecialCells(xlConstants, xlNumbers).Areas(2)
  Intersect(rng1.EntireRow, Columns("A:Z")).Sort Key1:=rng1, Order1:=xlAscending, Header:=xlNo
  Intersect(rng2.EntireRow, Columns("A:Z")).Sort Key1:=rng2, Order1:=xlAscending, Header:=xlNo
  rng1.Offset(, 1).Resize(, 2).Formula = Array( _
    "=INT(ABS(K2))&"" - ""&COUNTIF(L$1:L1,INT(ABS(K2))&"" -*"")+1", _
    "=IF(COUNTIF($L$2:$L$" & rng2.Row + rng2.Rows.Count - 1 & ",L2)=2,""x"","""")")
  rng2.Offset(, 1).Resize(, 2).Formula = Array( _
    Replace(Replace("=INT(K#)&"" - ""&COUNTIF(L$%:L%,INT(K#)&"" -*"")+1", "#", rng2.Row), "%", rng2.Row - 1), _
    "=IF(COUNTIF($L$2:$L$" & rng2.Row + rng2.Rows.Count - 1 & ",L" & rng2.Row & ")=2,""x"","""")")
End Sub

 

[bassemjohn1]

@Peter_SSs As usual, Brilliant! worked perfectly.

Thank you so much sir!

 

[Peter_SSs]

You're welcome.

You could also consider this which puts a slightly more efficient formula in column L

Sub SortPlusFormulas_v2()
  Dim rng1 As Range, rng2 As Range
  
  Set rng1 = Columns("K").SpecialCells(xlConstants, xlNumbers).Areas(1)
  Set rng2 = Columns("K").SpecialCells(xlConstants, xlNumbers).Areas(2)
  Intersect(rng1.EntireRow, Columns("A:Z")).Sort Key1:=rng1, Order1:=xlAscending, Header:=xlNo
  Intersect(rng2.EntireRow, Columns("A:Z")).Sort Key1:=rng2, Order1:=xlAscending, Header:=xlNo
  rng1.Offset(, 1).Resize(, 2).Formula = Array( _
    "=INT(ABS(K2))&"" - ""&COUNTIF(L$1:L1,INT(ABS(K2))&"" -*"")+1", _
    "=IF(ISNUMBER(MATCH(L2," & rng2.Offset(, 1).Address & ",0)),""x"","""")")
  rng2.Offset(, 1).Resize(, 2).Formula = Array( _
    Replace(Replace("=INT(K#)&"" - ""&COUNTIF(L$%:L%,INT(K#)&"" -*"")+1", "#", rng2.Row), "%", rng2.Row - 1), _
    "=IF(ISNUMBER(MATCH(L" & rng2.Row & "," & rng1.Offset(, 1).Address & ",0)),""x"","""")")
End Sub