lookup match return closest to x date

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Happy New Year, this is my first post in 2022.

I have two worksheets I'm working with.

worksheet one is titled KPI Data - MY22 ONLY
It is comprised of a list of unique data starting at F3 and a date starting at I3.

The other worksheet (CR 11.20 to 1.9) is comprised of data starting at A2 and a date starting at B2.

What I'm trying to do is match the data in KPI Data - MY22 ONLY with the data in CR 11.20 to 1.9, the kicker is that I'm looking for the date in column B that is closest (+/-) to the date in column I, then return the difference between those two dates in KPI Data - MY22 ONLY Column C.

I sure hope that makes sense :unsure:

I'm only pasting the first 100 lines of data. There is actually 21,182, so you may not find a match.

Book1.xlsx
AB
1DataDate
2WA1EAAFY5N202708911/20/2021
3WAUC4CF51NA00443411/20/2021
4WAUCBCF56NA00888711/20/2021
5WA1EAAFY4N203208711/20/2021
6WA1EECF33N101503311/20/2021
7WA1GAAFY0N203426511/20/2021
8WA1LAAGE4NB00670111/20/2021
9WAU92BF2XNN01431011/20/2021
10WAU72BF20NN01468011/20/2021
11WA1GAAFY0N202910111/20/2021
12WA1AUCF38N103788511/20/2021
13WA1BAAFY2M213820211/20/2021
14WA1EECF38N101661511/20/2021
15WA1LJBF75ND00354511/20/2021
16WAUABCF53NA00554811/20/2021
17WAUW4GF52NN00102311/20/2021
18WA1BNAFY9L203561911/20/2021
19WA1EECF31N101797611/20/2021
20WA1EECF34N103136811/20/2021
21WA12AAGE2NB00923611/20/2021
22WAUBBCF57NA00377411/20/2021
23WAUABCF50NA00471411/20/2021
24WA1DECF39N103313011/20/2021
25WA15AAFY3N200492711/20/2021
26WA1DECF37N101814211/20/2021
27WA1EAAFY8N200572011/20/2021
28WA1VAAF76HD02672711/20/2021
29WA1AAAFY8M213059811/20/2021
30WA1LAAGE7NB00947711/20/2021
31WA124AFY3N202893211/20/2021
32WA1AAAFY7M211688511/20/2021
33WA1LAAGEXNB00907011/20/2021
34WA1EAAFY8N202693811/20/2021
35WAUEJBFW7N700319211/20/2021
36WA1B4AFYXN203904011/20/2021
37WAUH3DGY4NA00511611/20/2021
38WA1EECF32N100791211/20/2021
39WA1AAAGE9NB00555211/20/2021
40WA1EECF39N101914911/20/2021
41WA1C4AFYXM209111111/20/2021
42WA1EECF3XN101872311/20/2021
43WAUCBCF50NA00608311/20/2021
44WA1EAAFY5N201674111/20/2021
45WAUABCF57NA00605511/20/2021
46WA1AAAGE7NB01096111/20/2021
47WAUY4GF57NN00137911/20/2021
48WA1AAAGE0NB01083411/20/2021
49WA1B4AFY6N203126011/20/2021
50WA1EAAFYXN202611611/20/2021
51WA1LAAGE5NB01175911/20/2021
52WA1LJBF71ND00120911/20/2021
53WA1EECF32N100626011/20/2021
54WAUDFAF28MN07653711/20/2021
55WA1C4AFY5N203189811/20/2021
56WAU8EAF87MN00490211/20/2021
57WAUY4GF50NN00131911/20/2021
58WA1VWBF79ND00008911/20/2021
59WUAPCBF25NN90074111/20/2021
60WA1AAAFY2M213787111/20/2021
61WAUH3DGYXNA00400411/20/2021
62WA1EECF38N102972211/20/2021
63WA14AAFY0N200086311/20/2021
64WAUABCF54MA03085911/20/2021
65WA1AJBF72ND00415711/20/2021
66WA1EAAFY4N201640711/20/2021
67WA1AUCF3XN104114511/20/2021
68WA1GAAFY1N203103511/20/2021
69WAU8SAF89MN02745911/20/2021
70WAUV2BF28NN01403111/20/2021
71WA1EECF37N103209311/20/2021
72WAUY4GF5XNN00135811/20/2021
73WUA1CBF22NN90125411/20/2021
74WA1DECF38N103149611/20/2021
75WA1FAAFY1N200413711/20/2021
76WA1CVBF11ND00635911/20/2021
77WA1EECF30N104098711/20/2021
78WA1EECF33N103521711/20/2021
79WA1GAAFYXN203185711/20/2021
80WA1LAAGE5NB00938111/20/2021
81WA1LAAGE9NB00924011/20/2021
82WA1VXBF71ND00369411/20/2021
83WUAKBAFX3N790037311/20/2021
84WA1EECF35N103073211/20/2021
85WA1AUCF35N104140811/20/2021
86WA1GAAFY6N202926411/20/2021
87WAUFJBFW1N700442411/20/2021
88WA1EECF38N103025511/20/2021
89WAUY4GF55NN00116811/20/2021
90WA1B4AFY1N203549111/20/2021
91WA1EECF39N103316411/20/2021
92WA1GAAFY4N202921511/20/2021
93WA12AAGE2MB01509711/20/2021
94WAUBBAF41NN00348311/20/2021
95WA1AUCF3XN103759511/20/2021
96WA1EECF35N103330711/20/2021
97WA1GAAFY5N201623211/20/2021
98WA1LXBF78ND00355411/20/2021
99WAUABCF53NA00562911/20/2021
100WAUCBCF55MA06250411/20/2021
101WAUE3BF22NN01381211/20/2021
CR 11.20 to 1.9


Book1.xlsx
ABC
2DataDateDifference
3WA1EAAFY9N204609612/31/2021
4WA1DECF36N105799712/31/2021
5WA1DECF35N104702512/31/2021
6WAUABAF49NN00460412/31/2021
7WA1CWBF10ND00929612/31/2021
8WA1EABFY5N204723512/31/2021
9WA1FAAFY1N204348612/31/2021
10WAUGUDGY6NA01824712/31/2021
11WAUHUDGY6NA01834812/31/2021
12WAUW4GF55NN00167712/31/2021
13WA1C4AFYXN204151912/31/2021
14WAU72BF24NN01317512/31/2021
15WA1EECF30N104411712/31/2021
16WA1AAAGE9NB01114212/31/2021
17WAUCBAF41NN00242112/31/2021
18WA1DECF37N104423812/31/2021
19WA1A4AFY3N203780512/31/2021
20WAUFACF56NA01347412/31/2021
21WAUDAAF4XNN00418812/31/2021
22WA1AAAGE3NB01345312/31/2021
23WAUEAAF47NN00407212/31/2021
24WA1LAAGE3NB01309012/31/2021
25WA1AAAGE4NB01294312/31/2021
26WA1FVBF16ND00646612/31/2021
27WAUYAGF53NN00172712/31/2021
28WA1EAAFY2N204678112/31/2021
29WA1EECF37N104974912/31/2021
30WA1EECF31N105170712/31/2021
31WA17AAF40NA00660112/31/2021
32WA1LAAGE6NB01144612/31/2021
33WU1ARBF10ND00902912/31/2021
34WA1DECF31N105406712/31/2021
35WA1DECF30N105358712/31/2021
36WA1EAAFY9N204412312/31/2021
37WAUPFBF24NN01856012/31/2021
38WAUAUDGY6NA01814212/31/2021
39WA15AAFY7N204530412/31/2021
40WA1LXBF78ND00563012/31/2021
41WA124AFY5N203893012/31/2021
42WA124AFY7N204241412/31/2021
43WA134AFY7N203783312/31/2021
44WAUEAAF48NN00407812/31/2021
45WAUP4AF59NA01082712/31/2021
46WA1LXBF73ND00584312/31/2021
47WA1FAAFY9N204586012/31/2021
48WAUAHBFW1N790237912/31/2021
49WA1AUCF31N102042612/31/2021
50WA1EAAFYXN204443712/31/2021
51WA1EAAFY5N204511012/31/2021
52WAUV2BF23NN00883312/31/2021
53WAUW4GF59NN00173212/31/2021
54WA1LJBF72ND00434512/31/2021
55WAUABCF55NA01124012/31/2021
56WA1FVBF10ND00654412/31/2021
57WA1LXBF7XND00525612/31/2021
58WA1EECF35N105175712/31/2021
59WAUCBCF5XNA01133812/31/2021
60WA1B4AFY0N203940412/31/2021
61WAUABCF50NA00622212/31/2021
62WA1C4AFY8N204010912/31/2021
63WAUAUDGYXNA03010212/31/2021
64WA12ABGE3NB00848712/31/2021
65WA11AAGE2NB01385012/31/2021
66WA1DECF31N102170212/31/2021
67WA1LJBF7XND00529112/31/2021
68WA1DECF38N102912012/31/2021
69WAU92BF20NN01423512/31/2021
70WAUFJBFW5N700414912/31/2021
71WA1EECF36N105332412/31/2021
72WA1LXBF7XND00580712/31/2021
73WAUEAAF40NN00407412/31/2021
74WA1EECF3XN105122212/31/2021
75WA124AFY4N204264512/31/2021
76WA15AAFY6N204681612/31/2021
77WA1B4AFY3N204490512/31/2021
78WA1BVBF13ND00983912/31/2021
79WA1EECF3XN104512812/31/2021
80WA1EECF39N104503812/31/2021
81WA1VXBF70ND00572712/31/2021
82WAUBBAF45NN00371412/31/2021
83WA1FAAFY6N204300112/31/2021
84WAUFACF51NA01288012/31/2021
85WA1B4AFY1N204054512/31/2021
86WAUCJBFW6N700158512/31/2021
87WA1DECF34N104391912/31/2021
88WA1LJBF78ND00626212/31/2021
89WA1CWBF15ND00835412/31/2021
90WA1EAAFY7N204489512/31/2021
91WAUFJBFWXN700379012/31/2021
92WA1B4AFY8N204041412/31/2021
93WA1VABGE7NB01550512/31/2021
94WA1LXBF77ND00556812/31/2021
95WA1LJBF75ND00542512/31/2021
96WAUCBCF51NA01137312/31/2021
97WA1DECF39N103205712/31/2021
98WA1FAAFYXN204321312/31/2021
99WAUD3BF2XNN01659612/31/2021
100WA1LABGE1NB00725912/31/2021
101WA1EECF3XN104835312/31/2021
102WAUEAAF49NN00255112/31/2021
KPI Data - MY22 ONLY
 

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.

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
i was just asking if you used "option explicit", i didn't but of sp, then you have to declare are variables thus line becomes
VBA Code:
Dim x(), Res(), a, i, DictArr, it, c, r
But i think the application.sort is the problem for a 2016.
What happend with the code in #8 ? Crashed ?
I made the changes, I also tried your suggestion above. Both crashed on Application.Sort
 

BSALV

Active Member
Joined
Oct 31, 2010
Messages
303
Office Version
  1. 365
  2. 2013
  3. 2007
a 2016-friendly approach, i hope.
It does the critical "application.sort" in a worksheet.
So, it adds (and deletes afterwards) a sheet in which your code+date is combined, sorts them ascending and takes that result in memory
VBA Code:
Sub compairing()
     Dim Res(), MyMin(), ArrT1

     a = Range("TBL_1").Resize(, 2).Value2                      'read 1st table, first 2 columns
     Set sh = Sheets.Add                                        'add a new sheet
     sh.Range("C1").Value = "!|!"                               'very small value
     With sh.Range("A2").Resize(UBound(a), 2)
          .Value = a                                            'copy the array
          With .Offset(, 2).Resize(, 1)                         '3rd column
               .FormulaR1C1 = "=+LEFT(RC[-2]&REPT("" "",50),20) & ""|"" &TEXT(RC[-1],""00000"")& ""|"" & row(rc)-1"     'combining the 2 columns = code & numeric date & listrow number
               .Value = .Value                                  ' replace by value
               With .Offset(-1).Resize(.Rows.Count + 1)         'add a very small element in front, so that you can find a match for the 1st sorted
                    .Sort .Range("A1")                          'sort this column
                    ArrT1 = .Value                              'read to an array
               End With
          End With
     End With
     Application.DisplayAlerts = False
     sh.Delete                                                  'delete new sheet
     Application.DisplayAlerts = True

     leeg = WorksheetFunction.Rept(" ", 50)
     Set C = Range("TBL_2").Resize(, 2)                         'range 2nd table
     a = C.Value2                                               'read 2nd table
     ReDim Res(1 To UBound(a), 1 To 3)                          'make resultat array
     For i = 1 To UBound(a)                                     'loop through data
          s = Left(a(i, 1) & leeg, 20) & "|" & Format(a(i, 2), "00000")     'make same kind of string as above
          r = Application.Match(s, ArrT1, 1)                    'find exact match or just 1 line in front
          If IsNumeric(r) Then                                  '(exact)match found, check that and next record
               ReDim MyMin(1 To 2, 1 To 3)                      'clear auxiliary array with 4 elements
               For k = 1 To 2: MyMin(k, 1) = 10000000000#: Next
               sp = Split(ArrT1(r, 1), "|")                     'split found record
               If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(1, 1) = Abs(sp(1) - a(i, 2)): MyMin(1, 2) = --sp(1): MyMin(1, 3) = ArrT1(r, 1)     'is name the same, then store abs(difference) and date
               If r < UBound(ArrT1) Then                        'if you're not at the last record
                    sp = Split(ArrT1(r + 1, 1), "|")            'take next record
                    If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(2, 1) = Abs(sp(1) - a(i, 2)): MyMin(2, 2) = --sp(1): MyMin(2, 3) = ArrT1(r + 1, 1)     'if name is the same, then store abs(difference) and date
               End If
               x = Application.Min(MyMin(1, 1), MyMin(2, 1))
               If x < 10000 Then
                    k = IIf(x = MyMin(1, 1), 1, 2)              'of both, who is the smallest, the 1st or the 2nd
                    Res(i, 1) = MyMin(k, 1)                     'difference
                    Res(i, 2) = MyMin(k, 2)                     'date
                    Res(i, 3) = MyMin(k, 3)                     'date
               End If
          End If
     Next

     C.Offset(, C.Columns.Count).Resize(, 3).Value = Res        'write result to table (3rd and eventually 4th column if 2 !!!
End Sub
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
791
Office Version
  1. 365
Platform
  1. Windows
If I understand you correctly, there are multiple dates for 1 number?
 
Last edited:

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
452

ADVERTISEMENT

A made some difference in yellow cell.
VBA Code:
Option Explicit
Sub test()
Dim LrC&, LrK&, i&, j&, k&, g&, dif&, min&
Dim cell As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim arrC, arrK(), f
Set ws1 = Worksheets("CR 11.20 to 1.9")
Set ws2 = Worksheets("KPI Data - MY22 only")
LrC = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LrK = ws2.Cells(Rows.Count, "F").End(xlUp).Row
ReDim arrK(1 To LrK, 1 To 1)
Set arrC = ws1.Range("A2:B" & LrC)
    For Each cell In ws2.Range("F3:F" & LrK)
        Set f = ws1.Range("A2:A" & LrC).Find(cell)
            If Not f Is Nothing Then
                dif = Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value)
                Do
                    Set f = ws1.Range("A2:A" & LrC).FindNext(f)
                        If Not f Is Nothing Then
                            k = k + 1
                            If dif > Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value) Then
                                min = Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value)
                                dif = min
                            End If
                        End If
                Loop Until k = LrC
            End If
    g = g + 1
    arrK(g, 1) = dif
    dif = 0
    Next
ws2.Range("J3").Resize(LrK - 2).Value = arrK
End Sub

Book1.xlsm
AB
1DataDate
2WA1EAAFY5N202708920/11/2021
3WA1DECF36N105799720/11/2021
4WA1DECF36N105799715/01/2022
5WA1DECF36N105799716/03/2021
6WA1EECF33N101503320/11/2021
7WA1DECF36N105799715/08/2021
8WA1LAAGE4NB00670120/11/2021
9WAU92BF2XNN01431020/11/2021
10WAU72BF20NN01468020/11/2021
11WA1GAAFY0N202910120/11/2021
12WA1AUCF38N103788520/11/2021
13WA1BAAFY2M213820220/11/2021
14WA1EECF38N101661520/11/2021
15WA1LJBF75ND00354520/11/2021
16WAUABCF53NA00554820/11/2021
17WAUW4GF52NN00102320/11/2021
18WA1BNAFY9L203561920/11/2021
19WA1EECF31N101797620/11/2021
20WA1EECF34N103136820/11/2021
21WA12AAGE2NB00923620/11/2021
22WAUBBCF57NA00377420/11/2021
23WAUABCF50NA00471420/11/2021
24WA1DECF39N103313020/11/2021
25WA15AAFY3N200492720/11/2021
26WA1DECF37N101814220/11/2021
27WA1EAAFY8N200572020/11/2021
28WA1VAAF76HD02672720/11/2021
29WA1AAAFY8M213059820/11/2021
30WA1LAAGE7NB00947720/11/2021
31WA124AFY3N202893220/11/2021
32WA1AAAFY7M211688520/11/2021
33WA1LAAGEXNB00907020/11/2021
34WA1EAAFY8N202693820/11/2021
35WAUEJBFW7N700319220/11/2021
36WA1B4AFYXN203904020/11/2021
37WAUH3DGY4NA00511620/11/2021
38WA1EECF32N100791220/11/2021
39WA1AAAGE9NB00555220/11/2021
40WA1EECF39N101914920/11/2021
41WA1C4AFYXM209111120/11/2021
42WA1EECF3XN101872320/11/2021
43WAUCBCF50NA00608320/11/2021
44WA1EAAFY5N201674120/11/2021
45WAUABCF57NA00605520/11/2021
46WA1AAAGE7NB01096120/11/2021
47WAUY4GF57NN00137920/11/2021
48WA1AAAGE0NB01083420/11/2021
49WA1B4AFY6N203126020/11/2021
50WA1EAAFYXN202611620/11/2021
51WA1LAAGE5NB01175920/11/2021
52WA1LJBF71ND00120920/11/2021
53WA1EECF32N100626020/11/2021
54WAUDFAF28MN07653720/11/2021
55WA1C4AFY5N203189820/11/2021
56WAU8EAF87MN00490220/11/2021
57WAUY4GF50NN00131920/11/2021
58WA1VWBF79ND00008920/11/2021
59WUAPCBF25NN90074120/11/2021
60WA1AAAFY2M213787120/11/2021
61WAUH3DGYXNA00400420/11/2021
62WA1EECF38N102972220/11/2021
63WA14AAFY0N200086320/11/2021
64WAUABCF54MA03085920/11/2021
65WA1AJBF72ND00415720/11/2021
66WA1EAAFY4N201640720/11/2021
67WA1AUCF3XN104114520/11/2021
68WA1GAAFY1N203103520/11/2021
69WAU8SAF89MN02745920/11/2021
70WAUV2BF28NN01403120/11/2021
71WA1EECF37N103209320/11/2021
72WAUY4GF5XNN00135820/11/2021
73WUA1CBF22NN90125420/11/2021
74WA1DECF38N103149620/11/2021
75WA1FAAFY1N200413720/11/2021
76WA1CVBF11ND00635920/11/2021
77WA1EECF30N104098720/11/2021
78WA1EECF33N103521720/11/2021
79WA1GAAFYXN203185720/11/2021
80WA1LAAGE5NB00938120/11/2021
81WA1LAAGE9NB00924020/11/2021
82WA1VXBF71ND00369420/11/2021
83WUAKBAFX3N790037320/11/2021
84WA1EECF35N103073220/11/2021
85WA1AUCF35N104140820/11/2021
86WA1GAAFY6N202926420/11/2021
87WAUFJBFW1N700442420/11/2021
88WA1EECF38N103025520/11/2021
89WAUY4GF55NN00116820/11/2021
90WA1B4AFY1N203549120/11/2021
91WA1EECF39N103316420/11/2021
92WA1GAAFY4N202921520/11/2021
93WA12AAGE2MB01509720/11/2021
94WAUBBAF41NN00348320/11/2021
95WA1AUCF3XN103759520/11/2021
96WA1EECF35N103330720/11/2021
97WA1GAAFY5N201623220/11/2021
98WA1LXBF78ND00355420/11/2021
99WAUABCF53NA00562920/11/2021
100WAUCBCF55MA06250420/11/2021
101WAUE3BF22NN01381220/11/2021
CR 11.20 to 1.9


Book1.xlsm
FGHIJ
2DataDateDifference
3WA1EAAFY9N204609631/12/20210
4WA1DECF36N105799731/12/202115
5WA1DECF35N104702531/12/20210
6WAUABAF49NN00460431/12/20210
7WA1CWBF10ND00929631/12/20210
8WA1EABFY5N204723531/12/20210
9WA1FAAFY1N204348631/12/20210
10WAUGUDGY6NA01824731/12/20210
11WAUHUDGY6NA01834831/12/20210
12WAUW4GF55NN00167731/12/20210
13WA1C4AFYXN204151931/12/20210
14WAU72BF24NN01317531/12/20210
15WA1EECF30N104411731/12/20210
16WA1AAAGE9NB01114231/12/20210
17WAUCBAF41NN00242131/12/20210
18WA1DECF37N104423831/12/20210
19WA1A4AFY3N203780531/12/20210
20WAUFACF56NA01347431/12/20210
21WAUDAAF4XNN00418831/12/20210
22WA1AAAGE3NB01345331/12/20210
23WAUEAAF47NN00407231/12/20210
24WA1LAAGE3NB01309031/12/20210
25WA1AAAGE4NB01294331/12/20210
26WA1FVBF16ND00646631/12/20210
27WAUYAGF53NN00172731/12/20210
28WA1EAAFY2N204678131/12/20210
29WA1EECF37N104974931/12/20210
30WA1EECF31N105170731/12/20210
31WA17AAF40NA00660131/12/20210
32WA1LAAGE6NB01144631/12/20210
33WU1ARBF10ND00902931/12/20210
34WA1DECF31N105406731/12/20210
35WA1DECF30N105358731/12/20210
36WA1EAAFY9N204412331/12/20210
37WAUPFBF24NN01856031/12/20210
38WAUAUDGY6NA01814231/12/20210
39WA15AAFY7N204530431/12/20210
40WA1LXBF78ND00563031/12/20210
41WA124AFY5N203893031/12/20210
42WA124AFY7N204241431/12/20210
43WA134AFY7N203783331/12/20210
44WAUEAAF48NN00407831/12/20210
45WAUP4AF59NA01082731/12/20210
46WA1LXBF73ND00584331/12/20210
47WA1FAAFY9N204586031/12/20210
48WAUAHBFW1N790237931/12/20210
49WA1AUCF31N102042631/12/20210
50WA1EAAFYXN204443731/12/20210
51WA1EAAFY5N204511031/12/20210
52WAUV2BF23NN00883331/12/20210
53WAUW4GF59NN00173231/12/20210
54WA1LJBF72ND00434531/12/20210
55WAUABCF55NA01124031/12/20210
56WA1FVBF10ND00654431/12/20210
57WA1LXBF7XND00525631/12/20210
58WA1EECF35N105175731/12/20210
59WAUCBCF5XNA01133831/12/20210
60WA1B4AFY0N203940431/12/20210
61WAUABCF50NA00622231/12/20210
62WA1C4AFY8N204010931/12/20210
63WAUAUDGYXNA03010231/12/20210
64WA12ABGE3NB00848731/12/20210
65WA11AAGE2NB01385031/12/20210
66WA1DECF31N102170231/12/20210
67WA1LJBF7XND00529131/12/20210
68WA1DECF38N102912031/12/20210
69WAU92BF20NN01423531/12/20210
70WAUFJBFW5N700414931/12/20210
71WA1EECF36N105332431/12/20210
72WA1LXBF7XND00580731/12/20210
73WAUEAAF40NN00407431/12/20210
74WA1EECF3XN105122231/12/20210
75WA124AFY4N204264531/12/20210
76WA15AAFY6N204681631/12/20210
77WA1B4AFY3N204490531/12/20210
78WA1BVBF13ND00983931/12/20210
79WA1EECF3XN104512831/12/20210
80WA1EECF39N104503831/12/20210
81WA1VXBF70ND00572731/12/20210
82WAUBBAF45NN00371431/12/20210
83WA1FAAFY6N204300131/12/20210
84WAUFACF51NA01288031/12/20210
85WA1B4AFY1N204054531/12/20210
86WAUCJBFW6N700158531/12/20210
87WA1DECF34N104391931/12/20210
88WA1LJBF78ND00626231/12/20210
89WA1CWBF15ND00835431/12/20210
90WA1EAAFY7N204489531/12/20210
91WAUFJBFWXN700379031/12/20210
92WA1B4AFY8N204041431/12/20210
93WA1VABGE7NB01550531/12/20210
94WA1LXBF77ND00556831/12/20210
95WA1LJBF75ND00542531/12/20210
96WAUCBCF51NA01137331/12/20210
97WA1DECF39N103205731/12/20210
98WA1FAAFYXN204321331/12/20210
99WAUD3BF2XNN01659631/12/20210
100WA1LABGE1NB00725931/12/20210
101WA1EECF3XN104835331/12/20210
102WAUEAAF49NN00255131/12/20210
KPI Data - MY22 only
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
If I understand you correctly, there are multiple dates for 1 number?
On the right side table is the list of master numbers, there are no duplicates. On the left is a list of numbers, many are duplicates and most should match up to one number on the right column.
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

a 2016-friendly approach, i hope.
It does the critical "application.sort" in a worksheet.
So, it adds (and deletes afterwards) a sheet in which your code+date is combined, sorts them ascending and takes that result in memory
VBA Code:
Sub compairing()
     Dim Res(), MyMin(), ArrT1

     a = Range("TBL_1").Resize(, 2).Value2                      'read 1st table, first 2 columns
     Set sh = Sheets.Add                                        'add a new sheet
     sh.Range("C1").Value = "!|!"                               'very small value
     With sh.Range("A2").Resize(UBound(a), 2)
          .Value = a                                            'copy the array
          With .Offset(, 2).Resize(, 1)                         '3rd column
               .FormulaR1C1 = "=+LEFT(RC[-2]&REPT("" "",50),20) & ""|"" &TEXT(RC[-1],""00000"")& ""|"" & row(rc)-1"     'combining the 2 columns = code & numeric date & listrow number
               .Value = .Value                                  ' replace by value
               With .Offset(-1).Resize(.Rows.Count + 1)         'add a very small element in front, so that you can find a match for the 1st sorted
                    .Sort .Range("A1")                          'sort this column
                    ArrT1 = .Value                              'read to an array
               End With
          End With
     End With
     Application.DisplayAlerts = False
     sh.Delete                                                  'delete new sheet
     Application.DisplayAlerts = True

     leeg = WorksheetFunction.Rept(" ", 50)
     Set C = Range("TBL_2").Resize(, 2)                         'range 2nd table
     a = C.Value2                                               'read 2nd table
     ReDim Res(1 To UBound(a), 1 To 3)                          'make resultat array
     For i = 1 To UBound(a)                                     'loop through data
          s = Left(a(i, 1) & leeg, 20) & "|" & Format(a(i, 2), "00000")     'make same kind of string as above
          r = Application.Match(s, ArrT1, 1)                    'find exact match or just 1 line in front
          If IsNumeric(r) Then                                  '(exact)match found, check that and next record
               ReDim MyMin(1 To 2, 1 To 3)                      'clear auxiliary array with 4 elements
               For k = 1 To 2: MyMin(k, 1) = 10000000000#: Next
               sp = Split(ArrT1(r, 1), "|")                     'split found record
               If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(1, 1) = Abs(sp(1) - a(i, 2)): MyMin(1, 2) = --sp(1): MyMin(1, 3) = ArrT1(r, 1)     'is name the same, then store abs(difference) and date
               If r < UBound(ArrT1) Then                        'if you're not at the last record
                    sp = Split(ArrT1(r + 1, 1), "|")            'take next record
                    If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(2, 1) = Abs(sp(1) - a(i, 2)): MyMin(2, 2) = --sp(1): MyMin(2, 3) = ArrT1(r + 1, 1)     'if name is the same, then store abs(difference) and date
               End If
               x = Application.Min(MyMin(1, 1), MyMin(2, 1))
               If x < 10000 Then
                    k = IIf(x = MyMin(1, 1), 1, 2)              'of both, who is the smallest, the 1st or the 2nd
                    Res(i, 1) = MyMin(k, 1)                     'difference
                    Res(i, 2) = MyMin(k, 2)                     'date
                    Res(i, 3) = MyMin(k, 3)                     'date
               End If
          End If
     Next

     C.Offset(, C.Columns.Count).Resize(, 3).Value = Res        'write result to table (3rd and eventually 4th column if 2 !!!
End Sub
Either this is taking a while, or it froze Excel :confused:
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
a 2016-friendly approach, i hope.
It does the critical "application.sort" in a worksheet.
So, it adds (and deletes afterwards) a sheet in which your code+date is combined, sorts them ascending and takes that result in memory
VBA Code:
Sub compairing()
     Dim Res(), MyMin(), ArrT1

     a = Range("TBL_1").Resize(, 2).Value2                      'read 1st table, first 2 columns
     Set sh = Sheets.Add                                        'add a new sheet
     sh.Range("C1").Value = "!|!"                               'very small value
     With sh.Range("A2").Resize(UBound(a), 2)
          .Value = a                                            'copy the array
          With .Offset(, 2).Resize(, 1)                         '3rd column
               .FormulaR1C1 = "=+LEFT(RC[-2]&REPT("" "",50),20) & ""|"" &TEXT(RC[-1],""00000"")& ""|"" & row(rc)-1"     'combining the 2 columns = code & numeric date & listrow number
               .Value = .Value                                  ' replace by value
               With .Offset(-1).Resize(.Rows.Count + 1)         'add a very small element in front, so that you can find a match for the 1st sorted
                    .Sort .Range("A1")                          'sort this column
                    ArrT1 = .Value                              'read to an array
               End With
          End With
     End With
     Application.DisplayAlerts = False
     sh.Delete                                                  'delete new sheet
     Application.DisplayAlerts = True

     leeg = WorksheetFunction.Rept(" ", 50)
     Set C = Range("TBL_2").Resize(, 2)                         'range 2nd table
     a = C.Value2                                               'read 2nd table
     ReDim Res(1 To UBound(a), 1 To 3)                          'make resultat array
     For i = 1 To UBound(a)                                     'loop through data
          s = Left(a(i, 1) & leeg, 20) & "|" & Format(a(i, 2), "00000")     'make same kind of string as above
          r = Application.Match(s, ArrT1, 1)                    'find exact match or just 1 line in front
          If IsNumeric(r) Then                                  '(exact)match found, check that and next record
               ReDim MyMin(1 To 2, 1 To 3)                      'clear auxiliary array with 4 elements
               For k = 1 To 2: MyMin(k, 1) = 10000000000#: Next
               sp = Split(ArrT1(r, 1), "|")                     'split found record
               If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(1, 1) = Abs(sp(1) - a(i, 2)): MyMin(1, 2) = --sp(1): MyMin(1, 3) = ArrT1(r, 1)     'is name the same, then store abs(difference) and date
               If r < UBound(ArrT1) Then                        'if you're not at the last record
                    sp = Split(ArrT1(r + 1, 1), "|")            'take next record
                    If (StrComp(Trim(sp(0)), Trim(a(i, 1)), vbTextCompare) = 0) Then MyMin(2, 1) = Abs(sp(1) - a(i, 2)): MyMin(2, 2) = --sp(1): MyMin(2, 3) = ArrT1(r + 1, 1)     'if name is the same, then store abs(difference) and date
               End If
               x = Application.Min(MyMin(1, 1), MyMin(2, 1))
               If x < 10000 Then
                    k = IIf(x = MyMin(1, 1), 1, 2)              'of both, who is the smallest, the 1st or the 2nd
                    Res(i, 1) = MyMin(k, 1)                     'difference
                    Res(i, 2) = MyMin(k, 2)                     'date
                    Res(i, 3) = MyMin(k, 3)                     'date
               End If
          End If
     Next

     C.Offset(, C.Columns.Count).Resize(, 3).Value = Res        'write result to table (3rd and eventually 4th column if 2 !!!
End Sub
I think it worked. I'll have to manually soft the data and check, but on the surface it looks good. Thank you!

Book1.xlsx
ABCDEFGHIJKL
1DataDateDataDateDifferenceColumn1Column2
2WA1EAAFY5N202708911/20/2021WA1EAAFY9N204609612/31/2021444565WA1EAAFY9N2046096 |44565|19398
3WAUC4CF51NA00443411/20/2021WA1DECF36N105799712/31/2021344564WA1DECF36N1057997 |44564|19093
4WAUCBCF56NA00888711/20/2021WA1DECF35N104702512/31/2021544566WA1DECF35N1047025 |44566|19966
5WA1EAAFY4N203208711/20/2021WAUABAF49NN00460412/31/2021
6WA1EECF33N101503311/20/2021WA1CWBF10ND00929612/31/2021
7WA1GAAFY0N203426511/20/2021WA1EABFY5N204723512/31/2021444565WA1EABFY5N2047235 |44565|19442
8WA1LAAGE4NB00670111/20/2021WA1FAAFY1N204348612/31/2021
9WAU92BF2XNN01431011/20/2021WAUGUDGY6NA01824712/31/2021
10WAU72BF20NN01468011/20/2021WAUHUDGY6NA01834812/31/2021444565WAUHUDGY6NA018348 |44565|19372
11WA1GAAFY0N202910111/20/2021WAUW4GF55NN00167712/31/2021
12WA1AUCF38N103788511/20/2021WA1C4AFYXN204151912/31/2021
13WA1BAAFY2M213820211/20/2021WAU72BF24NN01317512/31/2021744568WAU72BF24NN013175 |44568|20255
14WA1EECF38N101661511/20/2021WA1EECF30N104411712/31/2021
15WA1LJBF75ND00354511/20/2021WA1AAAGE9NB01114212/31/2021744568WA1AAAGE9NB011142 |44568|20129
16WAUABCF53NA00554811/20/2021WAUCBAF41NN00242112/31/2021
17WAUW4GF52NN00102311/20/2021WA1DECF37N104423812/31/2021544566WA1DECF37N1044238 |44566|20042
18WA1BNAFY9L203561911/20/2021WA1A4AFY3N203780512/31/2021444565WA1A4AFY3N2037805 |44565|19628
19WA1EECF31N101797611/20/2021WAUFACF56NA01347412/31/2021444565WAUFACF56NA013474 |44565|19243
20WA1EECF34N103136811/20/2021WAUDAAF4XNN00418812/31/2021
21WA12AAGE2NB00923611/20/2021WA1AAAGE3NB01345312/31/2021
22WAUBBCF57NA00377411/20/2021WAUEAAF47NN00407212/31/2021344564WAUEAAF47NN004072 |44564|19023
23WAUABCF50NA00471411/20/2021WA1LAAGE3NB01309012/31/2021
24WA1DECF39N103313011/20/2021WA1AAAGE4NB01294312/31/2021344564WA1AAAGE4NB012943 |44564|18997
25WA15AAFY3N200492711/20/2021WA1FVBF16ND00646612/31/2021344564WA1FVBF16ND006466 |44564|19044
26WA1DECF37N101814211/20/2021WAUYAGF53NN00172712/31/2021
27WA1EAAFY8N200572011/20/2021WA1EAAFY2N204678112/31/2021
28WA1VAAF76HD02672711/20/2021WA1EECF37N104974912/31/2021544566WA1EECF37N1049749 |44566|19876
29WA1AAAFY8M213059811/20/2021WA1EECF31N105170712/31/2021
30WA1LAAGE7NB00947711/20/2021WA17AAF40NA00660112/31/2021744568WA17AAF40NA006601 |44568|20340
KPI Data - MY22 ONLY
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
A made some difference in yellow cell.
VBA Code:
Option Explicit
Sub test()
Dim LrC&, LrK&, i&, j&, k&, g&, dif&, min&
Dim cell As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim arrC, arrK(), f
Set ws1 = Worksheets("CR 11.20 to 1.9")
Set ws2 = Worksheets("KPI Data - MY22 only")
LrC = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LrK = ws2.Cells(Rows.Count, "F").End(xlUp).Row
ReDim arrK(1 To LrK, 1 To 1)
Set arrC = ws1.Range("A2:B" & LrC)
    For Each cell In ws2.Range("F3:F" & LrK)
        Set f = ws1.Range("A2:A" & LrC).Find(cell)
            If Not f Is Nothing Then
                dif = Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value)
                Do
                    Set f = ws1.Range("A2:A" & LrC).FindNext(f)
                        If Not f Is Nothing Then
                            k = k + 1
                            If dif > Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value) Then
                                min = Abs(cell.Offset(, 3).Value - f.Offset(, 1).Value)
                                dif = min
                            End If
                        End If
                Loop Until k = LrC
            End If
    g = g + 1
    arrK(g, 1) = dif
    dif = 0
    Next
ws2.Range("J3").Resize(LrK - 2).Value = arrK
End Sub

Book1.xlsm
AB
1DataDate
2WA1EAAFY5N202708920/11/2021
3WA1DECF36N105799720/11/2021
4WA1DECF36N105799715/01/2022
5WA1DECF36N105799716/03/2021
6WA1EECF33N101503320/11/2021
7WA1DECF36N105799715/08/2021
8WA1LAAGE4NB00670120/11/2021
9WAU92BF2XNN01431020/11/2021
10WAU72BF20NN01468020/11/2021
11WA1GAAFY0N202910120/11/2021
12WA1AUCF38N103788520/11/2021
13WA1BAAFY2M213820220/11/2021
14WA1EECF38N101661520/11/2021
15WA1LJBF75ND00354520/11/2021
16WAUABCF53NA00554820/11/2021
17WAUW4GF52NN00102320/11/2021
18WA1BNAFY9L203561920/11/2021
19WA1EECF31N101797620/11/2021
20WA1EECF34N103136820/11/2021
21WA12AAGE2NB00923620/11/2021
22WAUBBCF57NA00377420/11/2021
23WAUABCF50NA00471420/11/2021
24WA1DECF39N103313020/11/2021
25WA15AAFY3N200492720/11/2021
26WA1DECF37N101814220/11/2021
27WA1EAAFY8N200572020/11/2021
28WA1VAAF76HD02672720/11/2021
29WA1AAAFY8M213059820/11/2021
30WA1LAAGE7NB00947720/11/2021
31WA124AFY3N202893220/11/2021
32WA1AAAFY7M211688520/11/2021
33WA1LAAGEXNB00907020/11/2021
34WA1EAAFY8N202693820/11/2021
35WAUEJBFW7N700319220/11/2021
36WA1B4AFYXN203904020/11/2021
37WAUH3DGY4NA00511620/11/2021
38WA1EECF32N100791220/11/2021
39WA1AAAGE9NB00555220/11/2021
40WA1EECF39N101914920/11/2021
41WA1C4AFYXM209111120/11/2021
42WA1EECF3XN101872320/11/2021
43WAUCBCF50NA00608320/11/2021
44WA1EAAFY5N201674120/11/2021
45WAUABCF57NA00605520/11/2021
46WA1AAAGE7NB01096120/11/2021
47WAUY4GF57NN00137920/11/2021
48WA1AAAGE0NB01083420/11/2021
49WA1B4AFY6N203126020/11/2021
50WA1EAAFYXN202611620/11/2021
51WA1LAAGE5NB01175920/11/2021
52WA1LJBF71ND00120920/11/2021
53WA1EECF32N100626020/11/2021
54WAUDFAF28MN07653720/11/2021
55WA1C4AFY5N203189820/11/2021
56WAU8EAF87MN00490220/11/2021
57WAUY4GF50NN00131920/11/2021
58WA1VWBF79ND00008920/11/2021
59WUAPCBF25NN90074120/11/2021
60WA1AAAFY2M213787120/11/2021
61WAUH3DGYXNA00400420/11/2021
62WA1EECF38N102972220/11/2021
63WA14AAFY0N200086320/11/2021
64WAUABCF54MA03085920/11/2021
65WA1AJBF72ND00415720/11/2021
66WA1EAAFY4N201640720/11/2021
67WA1AUCF3XN104114520/11/2021
68WA1GAAFY1N203103520/11/2021
69WAU8SAF89MN02745920/11/2021
70WAUV2BF28NN01403120/11/2021
71WA1EECF37N103209320/11/2021
72WAUY4GF5XNN00135820/11/2021
73WUA1CBF22NN90125420/11/2021
74WA1DECF38N103149620/11/2021
75WA1FAAFY1N200413720/11/2021
76WA1CVBF11ND00635920/11/2021
77WA1EECF30N104098720/11/2021
78WA1EECF33N103521720/11/2021
79WA1GAAFYXN203185720/11/2021
80WA1LAAGE5NB00938120/11/2021
81WA1LAAGE9NB00924020/11/2021
82WA1VXBF71ND00369420/11/2021
83WUAKBAFX3N790037320/11/2021
84WA1EECF35N103073220/11/2021
85WA1AUCF35N104140820/11/2021
86WA1GAAFY6N202926420/11/2021
87WAUFJBFW1N700442420/11/2021
88WA1EECF38N103025520/11/2021
89WAUY4GF55NN00116820/11/2021
90WA1B4AFY1N203549120/11/2021
91WA1EECF39N103316420/11/2021
92WA1GAAFY4N202921520/11/2021
93WA12AAGE2MB01509720/11/2021
94WAUBBAF41NN00348320/11/2021
95WA1AUCF3XN103759520/11/2021
96WA1EECF35N103330720/11/2021
97WA1GAAFY5N201623220/11/2021
98WA1LXBF78ND00355420/11/2021
99WAUABCF53NA00562920/11/2021
100WAUCBCF55MA06250420/11/2021
101WAUE3BF22NN01381220/11/2021
CR 11.20 to 1.9


Book1.xlsm
FGHIJ
2DataDateDifference
3WA1EAAFY9N204609631/12/20210
4WA1DECF36N105799731/12/202115
5WA1DECF35N104702531/12/20210
6WAUABAF49NN00460431/12/20210
7WA1CWBF10ND00929631/12/20210
8WA1EABFY5N204723531/12/20210
9WA1FAAFY1N204348631/12/20210
10WAUGUDGY6NA01824731/12/20210
11WAUHUDGY6NA01834831/12/20210
12WAUW4GF55NN00167731/12/20210
13WA1C4AFYXN204151931/12/20210
14WAU72BF24NN01317531/12/20210
15WA1EECF30N104411731/12/20210
16WA1AAAGE9NB01114231/12/20210
17WAUCBAF41NN00242131/12/20210
18WA1DECF37N104423831/12/20210
19WA1A4AFY3N203780531/12/20210
20WAUFACF56NA01347431/12/20210
21WAUDAAF4XNN00418831/12/20210
22WA1AAAGE3NB01345331/12/20210
23WAUEAAF47NN00407231/12/20210
24WA1LAAGE3NB01309031/12/20210
25WA1AAAGE4NB01294331/12/20210
26WA1FVBF16ND00646631/12/20210
27WAUYAGF53NN00172731/12/20210
28WA1EAAFY2N204678131/12/20210
29WA1EECF37N104974931/12/20210
30WA1EECF31N105170731/12/20210
31WA17AAF40NA00660131/12/20210
32WA1LAAGE6NB01144631/12/20210
33WU1ARBF10ND00902931/12/20210
34WA1DECF31N105406731/12/20210
35WA1DECF30N105358731/12/20210
36WA1EAAFY9N204412331/12/20210
37WAUPFBF24NN01856031/12/20210
38WAUAUDGY6NA01814231/12/20210
39WA15AAFY7N204530431/12/20210
40WA1LXBF78ND00563031/12/20210
41WA124AFY5N203893031/12/20210
42WA124AFY7N204241431/12/20210
43WA134AFY7N203783331/12/20210
44WAUEAAF48NN00407831/12/20210
45WAUP4AF59NA01082731/12/20210
46WA1LXBF73ND00584331/12/20210
47WA1FAAFY9N204586031/12/20210
48WAUAHBFW1N790237931/12/20210
49WA1AUCF31N102042631/12/20210
50WA1EAAFYXN204443731/12/20210
51WA1EAAFY5N204511031/12/20210
52WAUV2BF23NN00883331/12/20210
53WAUW4GF59NN00173231/12/20210
54WA1LJBF72ND00434531/12/20210
55WAUABCF55NA01124031/12/20210
56WA1FVBF10ND00654431/12/20210
57WA1LXBF7XND00525631/12/20210
58WA1EECF35N105175731/12/20210
59WAUCBCF5XNA01133831/12/20210
60WA1B4AFY0N203940431/12/20210
61WAUABCF50NA00622231/12/20210
62WA1C4AFY8N204010931/12/20210
63WAUAUDGYXNA03010231/12/20210
64WA12ABGE3NB00848731/12/20210
65WA11AAGE2NB01385031/12/20210
66WA1DECF31N102170231/12/20210
67WA1LJBF7XND00529131/12/20210
68WA1DECF38N102912031/12/20210
69WAU92BF20NN01423531/12/20210
70WAUFJBFW5N700414931/12/20210
71WA1EECF36N105332431/12/20210
72WA1LXBF7XND00580731/12/20210
73WAUEAAF40NN00407431/12/20210
74WA1EECF3XN105122231/12/20210
75WA124AFY4N204264531/12/20210
76WA15AAFY6N204681631/12/20210
77WA1B4AFY3N204490531/12/20210
78WA1BVBF13ND00983931/12/20210
79WA1EECF3XN104512831/12/20210
80WA1EECF39N104503831/12/20210
81WA1VXBF70ND00572731/12/20210
82WAUBBAF45NN00371431/12/20210
83WA1FAAFY6N204300131/12/20210
84WAUFACF51NA01288031/12/20210
85WA1B4AFY1N204054531/12/20210
86WAUCJBFW6N700158531/12/20210
87WA1DECF34N104391931/12/20210
88WA1LJBF78ND00626231/12/20210
89WA1CWBF15ND00835431/12/20210
90WA1EAAFY7N204489531/12/20210
91WAUFJBFWXN700379031/12/20210
92WA1B4AFY8N204041431/12/20210
93WA1VABGE7NB01550531/12/20210
94WA1LXBF77ND00556831/12/20210
95WA1LJBF75ND00542531/12/20210
96WAUCBCF51NA01137331/12/20210
97WA1DECF39N103205731/12/20210
98WA1FAAFYXN204321331/12/20210
99WAUD3BF2XNN01659631/12/20210
100WA1LABGE1NB00725931/12/20210
101WA1EECF3XN104835331/12/20210
102WAUEAAF49NN00255131/12/20210
KPI Data - MY22 only
Thank you for writing this keeping with my original structure with the two sheets. When I ran it I received a run-time error 9. Subscript out of range. arrK(g, 1) = dif is highlighted.
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
791
Office Version
  1. 365
Platform
  1. Windows
Try this dictionary method

VBA Code:
Sub jec()
 Dim ar, ar2, a As Variant, i As Long, j As Long
 ar = Sheets("KPI Data - MY22 ONLY").Cells(1, 1).CurrentRegion
 ar2 = Sheets("CR 11.20 to 1.9").Cells(1, 1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     For j = 2 To UBound(ar2)
        If Not .exists(ar(i, 1)) And ar(i, 1) = ar2(j, 1) Then
           .Item(ar(i, 1)) = Array(ar(i, 1), Abs(ar(i, 2) - ar2(j, 2)))
        ElseIf ar(i, 1) = ar2(j, 1) Then
            a = .Item(ar(i, 1))
            If Abs(ar(i, 2) - ar2(j, 2)) < a(1) Then a(1) = Abs(ar(i, 2) - ar2(j, 2))
           .Item(ar(i, 1)) = a
        End If
      Next
      If IsEmpty(.Item(ar(i, 1))) Then .Item(ar(i, 1)) = Array(ar(i, 1), "No match")
    Next
  Sheets("KPI Data - MY22 ONLY").Cells(2, 6).Resize(.Count) = Application.Index(.items, 0, 2)
 End With
End Sub
 
Solution
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

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