Help for matching nearest value

Hi all,

i having a data like 1000000,1100000,100000 in one column of excel and in another column data like 999965, 90000,109999

here need to math data in 2 columns if match not found i need to match with nearest data like 1000000= 999965.

help me with the solution.


Welcome to the community

  1. First read the first column into dt1 and second column into dt2 using 2 read range activities
  2. Use add data column and add a column sum to dt2 and another column to dt1 and match both column types are to be double
  3. Use a for each row in datatable and loop dt1
  4. Inside the loop use an assign activity dt2.Columns(0).Expression = "[ColumnNamedt2] - " + Cdbl(currentrow("Columnnamedt1").ToString)
    5.use another assign to sort the table
    dt2.AsEnumerable.orderby(function(x) math.abs(cdbl(x(0).Tostring))).CopyToDaTatable
  5. Now update match column of dt1 in assign
    `dt1.Columns(1) = cdbl(dt2.Rows(0)(0).Tostring)
  6. Use remove data row and remoce the first row in dt2

Now at the end you will have all the matched records in dt1

Hope this helps



Can you try the following sample?

dt1= dt1.AsEnumerable.Select(Function(r) dt1.Clone.LoadDataRow({r("Value1"),dt2.AsEnumerable.OrderBy(Function(r2) Math.Abs(CDbl(r2("Value2"))-CDbl(r("Value1")))).First().Item("Value2")},False)).CopyToDataTable() (7.7 KB)

If the above is not what you expect, can you share specific input and output?