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]

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"","""")"

Well noted, worked perfectly as i can see in column L, we rounded the numbers but if i don't want to round the numbers and just match based on the numbers before the decimals, can i do that ?
AS you can see in the below screenshot, the formula didn't match the -396.447 and 396.513 because it was rounded to 397

 

[Peter_SSs]

AS you can see in the below screenshot, the formula didn't match the -396.447 and 396.513 because it was rounded to 397

We could make those match by just ignoring the decimal part and looking at the whole number parts of the original numbers. In that case they would both be 396.
However, if we did that then -396.999 and 397.000 would not match because their whole number parts are different. yet these two numbers only differ by 0.001 yet the numbers highlighted yellow in your sample above differ by a larger amount of 0.066. Further, if we just ignore the decimals -396.000 and 396.999 would match even though they are almost 1 apart yet the very close numbers of -396.999 and 397.000 would not match.

That is why I asked earlier

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

 

[bassemjohn1]

We could make those match by just ignoring the decimal part and looking at the whole number parts of the original numbers. In that case they would both be 396.

okay now I got your point, I will go with that option though, can we ignore the decimals altogether without rounding our numbers and matching based on the whole number parts, what can I add to the formula to achieve these results?

Also Thanks again for your continous support.

 

[Peter_SSs]

can we ignore the decimals altogether without rounding our numbers and matching based on the whole number parts

Is this formula in column L better?

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

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

 

[bassemjohn1]

Is this formula in column L better?

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

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

That's exactly what am looking for, you are awesome, and am really thankful for your time! and all the info i learned from you.

 

[Peter_SSs]

Glad to help. Thanks for your kind words.

 

[bassemjohn1]

@Peter_SSs sorry to bother you, but may I ask you why when running my macro today, it didn't capture some values and offset them together

GL Intercompany.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-51.020	51 - 1	x
3	-2,092.372	2092 - 1	x
4	-149.340	149 - 1
5	-8,373.000	8373 - 1
6	-12.455	12 - 1
7	-1,677.060	1677 - 1	x
8	-121.843	121 - 1	x
9	-345.160	345 - 1
10	-2,978.755	2978 - 1	x
11	-149.460	149 - 1
12	-703.852	703 - 1	x
13	-627.188	627 - 1	x
14	-3,216.448	3216 - 1	x
15	-3,517.546	3517 - 1	x
16	-1,241.313	1241 - 1	x
17	-117.320	117 - 1	x
18	-77.249	77 - 1
19	-965.334	965 - 1	x
20	-834.041	834 - 1	x
21	-77.248	77 - 1
22	-37.698	37 - 1	x
23		0 - 0
24		0 - 0
25		0 - 0
26		0 - 0
27	Amount in local currency	#VALUE!
28	51.020	51 - 1	x
29	2,092.372	2092 - 1	x
30	149.340	149 - 1
31	121.840	121 - 1	x
32	2,978.755	2978 - 1	x
33	149.460	149 - 1
34	703.852	703 - 1	x
35	627.188	627 - 1	x
36	8,385.455	8385 - 1
37	3,216.448	3216 - 1	x
38	3,517.546	3517 - 1	x
39	1,241.313	1241 - 1	x
40	1,677.060	1677 - 1	x
41	117.320	117 - 1	x
42	77.249	77 - 1
43	965.334	965 - 1	x
44	834.041	834 - 1	x
45	77.249	77 - 2
46	37.698	37 - 1	x
BHC - BHR

Cell Formulas
Range	Formula
L2:L46	L2	=INT(ABS(K2))&" - "&COUNTIF(K$2:K2,K2)
M2:M46	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")

As you can see, it didn't highlight rows L3 and L33, Although they have the same values, 149-1 and 149-1, same with amount 77 in rows 18,21, and rows 42,45.

 

[Peter_SSs]

There is an issue with the Column L formula. However, to see what could be done about it, I would like to see the expected values in column L for that sample data.
Can you carefully fill in manually what values you would want in column L & column M and post that?
In particular, be careful with the '149' values and the '77' values since you have more than 2 of each of those in column K

 

[bassemjohn1]

There is an issue with the Column L formula. However, to see what could be done about it, I would like to see the expected values in column L for that sample data.
Can you carefully fill in manually what values you would want in column L & column M and post that?
In particular, be careful with the '149' values and the '77' values since you have more than 2 of each of those in column K

yes sir

GL Intercompany.XLSX
	K	L	M
1	Amount in local currency	Recon.	Match
2	-51.020	51 - 1	x
3	-2,092.372	2092 - 1	x
4	-149.340	149 - 1	x
5	-8,373.000	8373 - 1
6	-12.455	12 - 1
7	-1,677.060	1677 - 1	x
8	-121.843	121 - 1	x
9	-345.160	345 - 1
10	-2,978.755	2978 - 1	x
11	-149.460	149 - 1	x
12	-703.852	703 - 1	x
13	-627.188	627 - 1	x
14	-3,216.448	3216 - 1	x
15	-3,517.546	3517 - 1	x
16	-1,241.313	1241 - 1	x
17	-117.320	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 - 1	x
22	-37.698	37 - 1	x
23		0 - 0
24		0 - 0
25		0 - 0
26		0 - 0
27	Amount in local currency	#VALUE!
28	51.020	51 - 1	x
29	2,092.372	2092 - 1	x
30	149.340	149 - 1	x
31	121.840	121 - 1	x
32	2,978.755	2978 - 1	x
33	149.460	149 - 1	x
34	703.852	703 - 1	x
35	627.188	627 - 1	x
36	8,385.455	8385 - 1
37	3,216.448	3216 - 1	x
38	3,517.546	3517 - 1	x
39	1,241.313	1241 - 1	x
40	1,677.060	1677 - 1	x
41	117.320	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
BHC - BHR

Cell Formulas
Range	Formula
M46,M43:M44,M34:M41,M31:M32,M22:M29,M19:M20,M12:M17,M5:M10,M2:M3	M2	=IF(COUNTIF($L$2:$L$292,L2)=2,"x","")
L2:L46	L2	=INT(ABS(K2))&" - "&COUNTIF(K$2:K2,K2)

what to expect is highlighted in yellow, as -149.340 in row 3 should be offset against the positive value 149.340 in row 30, also -149.460 in row 11 should be offset against positive value 149.460 in row 33, and for 77.249 should be offset against the one in row 42. then in column M, every offset value from negative amounts against positive amounts should have x to be marked as done.

In particular, be careful with the '149' values and the '77' values since you have more than 2 of each of those in column K

that's the problem yes, that's why the formula didn't match these amounts against each other, what can we do so that even if I have 2 amounts in negative values that are the same to match with 2 amounts that are in positive value. like 77, 77 in L18 and L21 against -77 ,-77 in L42 and L45.

 

[Peter_SSs]

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