I need some help with a VBA Function?

brianclay111

New Member
Joined
Sep 6, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
1600030042434.png


Is there an easy way to do this? I'm trying to take the list from column D and compare each cell to the elements in column A, then compare the number next to column D(column E) and match that number with the closest match in column B that is in that group and write it to column G. I also need to do the same thing for column F. For Example, dog column E is 53, in column A there are 4 rows with dog and the closest number to 53 is 35, so the 35 would go in G2. Then the same for column F but would get written in H2 and so on.
I'm a little stumped on this one, any help would be appreciated. Thank You.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Check if you want the following results

varios 13sep2020.xlsm
ABCDEFGH
1
2dog35dog53133512
3dog12cat128237646
4dog4horse6716577
5dog76chicken16432345
6cat234cow3453512
7cat46
8cat76
9horse34
10horse54
11horse57
12horse2
13horse7
14chicken45
15chicken23
16chicken64
17chicken97
18cow35
19cow36
20cow37
21cow43
22cow12
23cow15
Hoja4


Here is another macro for you to consider:
VBA Code:
Sub closest_match()
  Dim dic As Object, c As Range, i As Long
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 2 To Range("D" & Rows.Count).End(3).Row
    dic(Range("D" & i).Value) = Array(i, Range("E" & i).Value, Range("F" & i).Value)
    Range("G" & i & ",H" & i).Value = 9 ^ 9
  Next
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    If Abs(c.Offset(, 1) - dic(c.Value)(1)) < Abs(Range("E" & dic(c.Value)(0)).Value - Range("G" & dic(c.Value)(0)).Value) Then _
      Range("G" & dic(c.Value)(0)).Value = c.Offset(, 1)
    If Abs(c.Offset(, 1) - dic(c.Value)(2)) < Abs(Range("F" & dic(c.Value)(0)).Value - Range("H" & dic(c.Value)(0)).Value) Then _
      Range("H" & dic(c.Value)(0)).Value = c.Offset(, 1)
  Next
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top