# Allocating Closest Match Numbers? VBA

#### JumboCactuar

##### Well-known Member
Hi,
if i have the following data (columns A-C):

 Name DefaultNo Allocated John 51 Jim 88 Tom 33 Luke 56 Paul 56 Frank 49 Bob 49 James 38 Trevor 56

and column F

 Available No 29-A 29-B 30-A 30-B 30-C 32-D 35-A 41-A 42-A 43-B 44-A 48-A 49-A 49-B 49-C 49-D 51-D 57-E 57-G 58-A 60-A 71-B

i want to allocate the closest available and then remove it from list
e.g if DefaultNo is 51
look through Available closest to 51 and take it from the list
repeat down

expected result:

 Name DefaultNo Allocated John 51 51-D Jim 88 71-B Tom 33 32-D Luke 56 57-E Paul 56 57-G Frank 49 49-A Bob 49 49-B James 38 41-A Trevor 56 58-A

any help appreciated

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### offthelip

##### Well-known Member
try this code:
VBA Code:
``````Sub test()
Dim numb As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
lastavail = Cells(Rows.Count, "F").End(xlUp).Row
avail = Range(Cells(1, 6), Cells(lastavail, 6))
For i = 2 To lastrow
Delta = 1E+20
indi = 0
For j = 2 To lastavail
numb = Left(avail(j, 1), 2)
tempdel = inarr(i, 2) - numb
If tempdel < 0 Then tempdel = -tempdel
If tempdel < Delta Then
Delta = tempdel
indi = j
End If
Next j
inarr(i, 3) = avail(indi, 1)
avail(indi, 1) = "00-AA"
Next i
Range(Cells(1, 1), Cells(lastrow, 3)) = inarr

End Sub``````

#### JumboCactuar

##### Well-known Member

Replies
3
Views
75
Replies
5
Views
239
Replies
4
Views
192
Replies
2
Views
577
Replies
42
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back