Help for matching nearest value

Help Something Else
1

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.
Thanks

2

@harun.yarramanayuni

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

Cheers

3

Hi,

Can you try the following sample?

image
image707×534 29.4 KB 

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()

Sample20221224-2aL.zip (7.7 KB)

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

Regards,