Good time of day. There is a task in Excel. It is necessary to compare two columns and select the closest value from the first for the second column.

Macro which I use
1 St; 2 St; result
1200; 1900; 1300
1300; 1200; 1200
2800; 2680;
840; 841; 840
830; 720;
750;
all the time different data. Desirable macro.
Applied a macro, but it does not correctly select values.

Sub Raspredlitel() Dim arrEt, arrMetka, arrMetka2, arrIncome Dim i%, iGlob%, j%, eps&, mJ%, mI% arrEt = Range([D4], [D10000].End(xlUp)).Value arrIncome = Range([K4], [K10000].End(xlUp)).Value ReDim arrMetka(1 To UBound(arrEt)) ReDim arrMetka2(1 To UBound(arrIncome)) For iGlob = 1 To UBound(arrIncome, 1) eps = 9 ^ 9 mJ = 1 mI = 1 For i = 1 To UBound(arrIncome, 1) If IsEmpty(arrMetka2(i)) Then For j = 1 To UBound(arrEt) If Abs(arrIncome(i, 1) - arrEt(j, 1)) < eps And IsEmpty(arrMetka(j)) Then mJ = j mI = i eps = Abs(arrIncome(i, 1) - arrEt(j, 1)) End If Next j End If Next i arrMetka(mJ) = 1 arrMetka2(mI) = 1 [M3].Offset(mI) = arrEt(mJ, 1) Next iGlob End Sub
  • How incorrect is the result indicated in the question? Try to start with iGlob == 4 just copy everything arrIncome with IsEmpty (arrMetka2 (i)) into some column, and also similarly for arrEt - Michael M

1 answer 1

Did not understand your code. Faster your write :)

 Option Explicit Sub Raspredlitel() Dim ArrMetka1, ArrMetka2, ArrTemp Dim lRws As Long Dim i As Long, j As Long With Worksheets("Лист1") lRws = .Cells(Rows.Count, "D").End(xlUp).Row + 1 ArrMetka1 = .Range("D4:D" & lRws).Value ' значения в массив lRws = .Cells(Rows.Count, "K").End(xlUp).Row + 1 ArrMetka2 = .Range("K4:K" & lRws).Value ' значения в массив ' задаем размерность массива ReDim ArrTemp(1 To UBound(ArrMetka2, 1) + 1, 1 To 1) For i = 1 To UBound(ArrMetka2, 1) ' запоминаем разность i-значения второго диапазона и первого значения первого диапазона ArrTemp(i + 1, 1) = Abs(ArrMetka2(i, 1) - ArrMetka1(1, 1)) ArrTemp(i, 1) = ArrMetka1(1, 1) ' запоминаем первое значение первого диапазона For j = 1 To UBound(ArrMetka1, 1) ' если разность меньше записанной, запоминаем меньшее If Abs(ArrMetka2(i, 1) - ArrMetka1(j, 1)) < ArrTemp(i + 1, 1) Then ArrTemp(i + 1, 1) = Abs(ArrMetka2(i, 1) - ArrMetka1(j, 1)) ArrTemp(i, 1) = ArrMetka1(j, 1) End If Next j Next i ' выгружаем массив на лист .Range("M4").Resize(UBound(ArrMetka2, 1), 1).Value = ArrTemp End With End Sub
  • Applied the script (code). The result received the same. Namely: there is a value, for example, 2629 and picks up 2851, but there is 2122 and there is for example the value 839, he chooses 840 and there is a value for him 749. And I still can’t figure out how to make those values ​​that fit he substituted in M4 but he cleaned with 1 column. - kadekin77
  • Your task: to choose the closest value from the first column to the value of the second column. In the first message, the word that the number should not be more (or less). That is, choose a number, the "distance" from which to the sample is smaller. 2629-2851 = -22; 2629-2122 = 507 222 less than 507 . Choose 2851 . 839-840 = 1; 749-839 = 90 What did not suit you? Doubt that your question is properly formulated. Why in your example from the first message there are no results near the values ​​of 2800 and 720 ? - vikttur