Match nearest values in two columns

Copper Contributor

Need to rearrange table based on column name Deducted amt by matching same/nearest values

 

 

Transaction DateS.NoAmount Paid / CreditedDeducted amtAs per MetroDeducted amt 
01-Apr-20191 48,728.00 975.002         17,983.00
01-Apr-20192 15,388.00 308.001              307.76
01-Apr-20193 8,99,150.00 17,983.006           9,586.20
01-Apr-20194 50,452.00 1,009.003           1,170.00
01-Apr-20195 17,608.00 352.003              990.00
11-Apr-20196 7,63,776.00 15,276.003           3,702.34
11-Apr-20197 4,25,136.00 8,503.003      1,23,529.70
11-Apr-20198 61,76,485.00 1,23,530.003         16,718.36
15-Apr-20199 1,85,117.00 3,702.003         15,275.52
17-Apr-201910 3,45,670.00 6,913.003           8,502.72
17-Apr-201911 20,925.00 418.004              418.50
17-Apr-201912 70,468.00 1,409.004           6,913.40
17-Apr-201913 3,50,586.00 7,012.004           1,409.36
17-Apr-201914 8,35,918.00 16,718.004           7,011.72
18-Apr-201915 58,500.00 1,170.005              230.82
18-Apr-201916 49,500.00 990.005         16,592.00
01-May-201917 4,79,310.00 9,586.005           2,149.80
07-May-201918 9,84,467.00 19,689.005           1,709.20
07-May-201919 1,26,396.00 2,528.005           2,527.92
07-May-201920 2,44,120.00 4,882.005         19,689.35
07-May-201921 56,14,996.00 1,12,300.005           4,882.39
07-May-201922 8,32,320.00 16,646.005      1,12,299.92
07-May-201923 10,60,796.00 21,216.005         16,646.40
20-May-201924 85,460.00 1,709.005         21,215.91
20-May-201925 11,541.00 231.007         12,955.73
20-May-201926 8,29,600.00 16,592.007         79,871.64
20-May-201927 1,07,490.00 2,150.007         13,317.12
23-May-201928 24,44,657.00 48,893.007         48,893.14
23-May-201929 6,65,856.00 13,317.007           3,134.58
23-May-201930 39,93,582.00 79,872.007           6,813.62
23-May-201931 6,47,786.00 12,956.008         13,492.62
23-May-201932 1,56,729.00 3,135.008           1,249.54
23-May-201933 3,40,681.00 6,814.009           1,326.40
23-May-201934 6,74,631.00 13,493.009              325.16
23-May-201935 62,477.00 1,250.009         15,223.40
30-May-201936 5,08,788.00 10,176.009           1,829.88
30-May-201937 63,71,436.00 1,27,429.009           2,752.20
30-May-201938 4,30,848.00 8,617.009              848.70
30-May-201939 14,80,784.00 29,616.009         10,895.40
01-Jun-201940 9,78,790.00 19,576.009           1,470.64
01-Jun-201941 78,700.00 1,574.009           6,458.12
01-Jun-201942 1,97,916.00 3,958.009      1,27,428.73
01-Jun-201943 18,043.00 361.009           8,616.96
04-Jun-201944 5,44,770.00 10,895.009         29,615.69
04-Jun-201945 7,61,170.00 15,223.009         10,175.77
04-Jun-201946 16,258.00 325.0010      1,44,822.34
04-Jun-201947 66,320.00 1,326.0010           9,987.84
04-Jun-201948 1,37,610.00 2,752.0010         12,118.84
04-Jun-201949 42,435.00 849.0010           5,560.02
04-Jun-201950 91,494.00 1,830.0010              740.00
04-Jun-201951 3,22,906.00 6,458.0010              700.00
04-Jun-201952 73,532.00 1,471.0010              710.00
12-Jun-201953 6,05,942.00 12,119.0011              715.58
12-Jun-201954 72,41,117.00 1,44,822.0011         17,221.20
12-Jun-201955 2,78,001.00 5,560.0011              920.68
12-Jun-201956 4,99,392.00 9,988.0011           3,958.32
20-Jun-201957 37,000.00 740.0011              360.86
20-Jun-201958 35,000.00 700.0011         19,575.80
01-Jul-201959 4,81,118.00 9,622.0011           1,574.00
01-Jul-201960 16,07,361.00 32,147.0011           9,622.37
01-Jul-201961 59,495.00 1,190.0011      1,21,573.35
01-Jul-201962 60,78,668.00 1,21,573.0011         12,533.76
01-Jul-201963 6,26,688.00 12,534.0011         32,147.22
02-Jul-201964 46,034.00 921.0011           1,189.90
02-Jul-201965 8,61,060.00 17,221.0012           2,449.97
02-Jul-201966 35,779.00 716.0012      1,20,215.48
13-Jul-201967 17,92,109.00 35,842.0012         12,533.76
13-Jul-201968 1,22,498.00 2,450.0012         35,842.19
13-Jul-201969 6,26,688.00 12,534.0057         16,373.80
13-Jul-201970 60,10,774.00 1,20,215.0057           1,103.36
25-Jul-201971 32,516.00 650.0014              650.32
25-Jul-201972 67,780.00 1,356.0014           1,355.60
25-Jul-201973 3,927.00 79.0014           4,167.40
25-Jul-201974 2,08,370.00 4,167.0014                 78.54
26-Jul-201975 76,166.00 1,523.0014              213.08
26-Jul-201976 79,25,944.00 1,58,519.0014         16,176.40
26-Jul-201977 5,67,936.00 11,359.0014           1,523.32
26-Jul-201978 39,337.00 787.0014           4,671.00
26-Jul-201979 2,33,550.00 4,671.0014              786.75
26-Jul-201980 10,654.00 213.0014      1,58,518.88
26-Jul-201981 8,08,820.00 16,176.0014         11,358.72
06-Aug-201982 63,33,256.00 1,26,665.0015         13,463.04
06-Aug-201983 5,87,520.00 11,750.0015      1,26,665.12
07-Aug-201984 6,73,152.00 13,463.0015         11,750.40
08-Aug-201985 63,402.00 1,268.0016                 47.00
1 Reply

@M_T_M_ 

 

You've had almost 60 views, no responses. Might I be so bold as to suggest that the reason for the lack of responses is that your post doesn't make total sense. You give an example of a close match from the second row of that lengthy table, but don't give any idea of the significance of that close match other than that it's an example of a close match--which is evident on the surface.

 

So the question to you is "So What?!"

 

Those two numbers are only 24 cents apart. Down a bit lower there are two numbers that are 50 cents apart. Again, one wonders "so what?!" What are you wanting to do. You say "rearrange the table" but there are multiple ways to rearrange a table; which rearrangement do you have in mind?

 

By the way, it's not good practice to have two columns in a table with the same heading.

 

You're far more likely to get a response if you explain what it is we're looking at, what the different columns and rows represent. Along the way, you might find it clearer to give a slightly different name to one of those two columns that currently are headed "Deducted amt"

 

And if you can do this, post a copy of the actual spreadsheet on OneDrive or GoogleDrive with a link pasted here that grants access.