lookup match return closest to x date

288enzo · Jan 11, 2022

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

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

Book1.xlsx

A

B

1

Data

Date

2

WA1EAAFY5N2027089

11/20/2021

3

WAUC4CF51NA004434

11/20/2021

4

WAUCBCF56NA008887

11/20/2021

5

WA1EAAFY4N2032087

11/20/2021

6

WA1EECF33N1015033

11/20/2021

7

WA1GAAFY0N2034265

11/20/2021

8

WA1LAAGE4NB006701

11/20/2021

9

WAU92BF2XNN014310

11/20/2021

10

WAU72BF20NN014680

11/20/2021

11

WA1GAAFY0N2029101

11/20/2021

12

WA1AUCF38N1037885

11/20/2021

13

WA1BAAFY2M2138202

11/20/2021

14

WA1EECF38N1016615

11/20/2021

15

WA1LJBF75ND003545

11/20/2021

16

WAUABCF53NA005548

11/20/2021

17

WAUW4GF52NN001023

11/20/2021

18

WA1BNAFY9L2035619

11/20/2021

19

WA1EECF31N1017976

11/20/2021

20

WA1EECF34N1031368

11/20/2021

21

WA12AAGE2NB009236

11/20/2021

22

WAUBBCF57NA003774

11/20/2021

23

WAUABCF50NA004714

11/20/2021

24

WA1DECF39N1033130

11/20/2021

25

WA15AAFY3N2004927

11/20/2021

26

WA1DECF37N1018142

11/20/2021

27

WA1EAAFY8N2005720

11/20/2021

28

WA1VAAF76HD026727

11/20/2021

29

WA1AAAFY8M2130598

11/20/2021

30

WA1LAAGE7NB009477

11/20/2021

31

WA124AFY3N2028932

11/20/2021

32

WA1AAAFY7M2116885

11/20/2021

33

WA1LAAGEXNB009070

11/20/2021

34

WA1EAAFY8N2026938

11/20/2021

35

WAUEJBFW7N7003192

11/20/2021

36

WA1B4AFYXN2039040

11/20/2021

37

WAUH3DGY4NA005116

11/20/2021

38

WA1EECF32N1007912

11/20/2021

39

WA1AAAGE9NB005552

11/20/2021

40

WA1EECF39N1019149

11/20/2021

41

WA1C4AFYXM2091111

11/20/2021

42

WA1EECF3XN1018723

11/20/2021

43

WAUCBCF50NA006083

11/20/2021

44

WA1EAAFY5N2016741

11/20/2021

45

WAUABCF57NA006055

11/20/2021

46

WA1AAAGE7NB010961

11/20/2021

47

WAUY4GF57NN001379

11/20/2021

48

WA1AAAGE0NB010834

11/20/2021

49

WA1B4AFY6N2031260

11/20/2021

50

WA1EAAFYXN2026116

11/20/2021

51

WA1LAAGE5NB011759

11/20/2021

52

WA1LJBF71ND001209

11/20/2021

53

WA1EECF32N1006260

11/20/2021

54

WAUDFAF28MN076537

11/20/2021

55

WA1C4AFY5N2031898

11/20/2021

56

WAU8EAF87MN004902

11/20/2021

57

WAUY4GF50NN001319

11/20/2021

58

WA1VWBF79ND000089

11/20/2021

59

WUAPCBF25NN900741

11/20/2021

60

WA1AAAFY2M2137871

11/20/2021

61

WAUH3DGYXNA004004

11/20/2021

62

WA1EECF38N1029722

11/20/2021

63

WA14AAFY0N2000863

11/20/2021

64

WAUABCF54MA030859

11/20/2021

65

WA1AJBF72ND004157

11/20/2021

66

WA1EAAFY4N2016407

11/20/2021

67

WA1AUCF3XN1041145

11/20/2021

68

WA1GAAFY1N2031035

11/20/2021

69

WAU8SAF89MN027459

11/20/2021

70

WAUV2BF28NN014031

11/20/2021

71

WA1EECF37N1032093

11/20/2021

72

WAUY4GF5XNN001358

11/20/2021

73

WUA1CBF22NN901254

11/20/2021

74

WA1DECF38N1031496

11/20/2021

75

WA1FAAFY1N2004137

11/20/2021

76

WA1CVBF11ND006359

11/20/2021

77

WA1EECF30N1040987

11/20/2021

78

WA1EECF33N1035217

11/20/2021

79

WA1GAAFYXN2031857

11/20/2021

80

WA1LAAGE5NB009381

11/20/2021

81

WA1LAAGE9NB009240

11/20/2021

82

WA1VXBF71ND003694

11/20/2021

83

WUAKBAFX3N7900373

11/20/2021

84

WA1EECF35N1030732

11/20/2021

85

WA1AUCF35N1041408

11/20/2021

86

WA1GAAFY6N2029264

11/20/2021

87

WAUFJBFW1N7004424

11/20/2021

88

WA1EECF38N1030255

11/20/2021

89

WAUY4GF55NN001168

11/20/2021

90

WA1B4AFY1N2035491

11/20/2021

91

WA1EECF39N1033164

11/20/2021

92

WA1GAAFY4N2029215

11/20/2021

93

WA12AAGE2MB015097

11/20/2021

94

WAUBBAF41NN003483

11/20/2021

95

WA1AUCF3XN1037595

11/20/2021

96

WA1EECF35N1033307

11/20/2021

97

WA1GAAFY5N2016232

11/20/2021

98

WA1LXBF78ND003554

11/20/2021

99

WAUABCF53NA005629

11/20/2021

100

WAUCBCF55MA062504

11/20/2021

101

WAUE3BF22NN013812

11/20/2021

CR 11.20 to 1.9

Book1.xlsx

A

B

C

2

Data

Date

Difference

3

WA1EAAFY9N2046096

12/31/2021

4

WA1DECF36N1057997

12/31/2021

5

WA1DECF35N1047025

12/31/2021

6

WAUABAF49NN004604

12/31/2021

7

WA1CWBF10ND009296

12/31/2021

8

WA1EABFY5N2047235

12/31/2021

9

WA1FAAFY1N2043486

12/31/2021

10

WAUGUDGY6NA018247

12/31/2021

11

WAUHUDGY6NA018348

12/31/2021

12

WAUW4GF55NN001677

12/31/2021

13

WA1C4AFYXN2041519

12/31/2021

14

WAU72BF24NN013175

12/31/2021

15

WA1EECF30N1044117

12/31/2021

16

WA1AAAGE9NB011142

12/31/2021

17

WAUCBAF41NN002421

12/31/2021

18

WA1DECF37N1044238

12/31/2021

19

WA1A4AFY3N2037805

12/31/2021

20

WAUFACF56NA013474

12/31/2021

21

WAUDAAF4XNN004188

12/31/2021

22

WA1AAAGE3NB013453

12/31/2021

23

WAUEAAF47NN004072

12/31/2021

24

WA1LAAGE3NB013090

12/31/2021

25

WA1AAAGE4NB012943

12/31/2021

26

WA1FVBF16ND006466

12/31/2021

27

WAUYAGF53NN001727

12/31/2021

28

WA1EAAFY2N2046781

12/31/2021

29

WA1EECF37N1049749

12/31/2021

30

WA1EECF31N1051707

12/31/2021

31

WA17AAF40NA006601

12/31/2021

32

WA1LAAGE6NB011446

12/31/2021

33

WU1ARBF10ND009029

12/31/2021

34

WA1DECF31N1054067

12/31/2021

35

WA1DECF30N1053587

12/31/2021

36

WA1EAAFY9N2044123

12/31/2021

37

WAUPFBF24NN018560

12/31/2021

38

WAUAUDGY6NA018142

12/31/2021

39

WA15AAFY7N2045304

12/31/2021

40

WA1LXBF78ND005630

12/31/2021

41

WA124AFY5N2038930

12/31/2021

42

WA124AFY7N2042414

12/31/2021

43

WA134AFY7N2037833

12/31/2021

44

WAUEAAF48NN004078

12/31/2021

45

WAUP4AF59NA010827

12/31/2021

46

WA1LXBF73ND005843

12/31/2021

47

WA1FAAFY9N2045860

12/31/2021

48

WAUAHBFW1N7902379

12/31/2021

49

WA1AUCF31N1020426

12/31/2021

50

WA1EAAFYXN2044437

12/31/2021

51

WA1EAAFY5N2045110

12/31/2021

52

WAUV2BF23NN008833

12/31/2021

53

WAUW4GF59NN001732

12/31/2021

54

WA1LJBF72ND004345

12/31/2021

55

WAUABCF55NA011240

12/31/2021

56

WA1FVBF10ND006544

12/31/2021

57

WA1LXBF7XND005256

12/31/2021

58

WA1EECF35N1051757

12/31/2021

59

WAUCBCF5XNA011338

12/31/2021

60

WA1B4AFY0N2039404

12/31/2021

61

WAUABCF50NA006222

12/31/2021

62

WA1C4AFY8N2040109

12/31/2021

63

WAUAUDGYXNA030102

12/31/2021

64

WA12ABGE3NB008487

12/31/2021

65

WA11AAGE2NB013850

12/31/2021

66

WA1DECF31N1021702

12/31/2021

67

WA1LJBF7XND005291

12/31/2021

68

WA1DECF38N1029120

12/31/2021

69

WAU92BF20NN014235

12/31/2021

70

WAUFJBFW5N7004149

12/31/2021

71

WA1EECF36N1053324

12/31/2021

72

WA1LXBF7XND005807

12/31/2021

73

WAUEAAF40NN004074

12/31/2021

74

WA1EECF3XN1051222

12/31/2021

75

WA124AFY4N2042645

12/31/2021

76

WA15AAFY6N2046816

12/31/2021

77

WA1B4AFY3N2044905

12/31/2021

78

WA1BVBF13ND009839

12/31/2021

79

WA1EECF3XN1045128

12/31/2021

80

WA1EECF39N1045038

12/31/2021

81

WA1VXBF70ND005727

12/31/2021

82

WAUBBAF45NN003714

12/31/2021

83

WA1FAAFY6N2043001

12/31/2021

84

WAUFACF51NA012880

12/31/2021

85

WA1B4AFY1N2040545

12/31/2021

86

WAUCJBFW6N7001585

12/31/2021

87

WA1DECF34N1043919

12/31/2021

88

WA1LJBF78ND006262

12/31/2021

89

WA1CWBF15ND008354

12/31/2021

90

WA1EAAFY7N2044895

12/31/2021

91

WAUFJBFWXN7003790

12/31/2021

92

WA1B4AFY8N2040414

12/31/2021

93

WA1VABGE7NB015505

12/31/2021

94

WA1LXBF77ND005568

12/31/2021

95

WA1LJBF75ND005425

12/31/2021

96

WAUCBCF51NA011373

12/31/2021

97

WA1DECF39N1032057

12/31/2021

98

WA1FAAFYXN2043213

12/31/2021

99

WAUD3BF2XNN016596

12/31/2021

100

WA1LABGE1NB007259

12/31/2021

101

WA1EECF3XN1048353

12/31/2021

102

WAUEAAF49NN002551

12/31/2021

KPI Data - MY22 ONLY

Fluff · Jan 12, 2022

BSALV said:
But i think the application.sort is the problem for a 2016.

Yes it is, that's only in 365 & 2021

288enzo · Jan 12, 2022

BSALV said:
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 · Jan 13, 2022

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 · Jan 13, 2022

If I understand you correctly, there are multiple dates for 1 number?

bebo021999 · Jan 13, 2022

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

A

B

1

Data

Date

2

WA1EAAFY5N2027089

20/11/2021

3

WA1DECF36N1057997

20/11/2021

4

WA1DECF36N1057997

15/01/2022

5

WA1DECF36N1057997

16/03/2021

6

WA1EECF33N1015033

20/11/2021

7

WA1DECF36N1057997

15/08/2021

8

WA1LAAGE4NB006701

20/11/2021

9

WAU92BF2XNN014310

20/11/2021

10

WAU72BF20NN014680

20/11/2021

11

WA1GAAFY0N2029101

20/11/2021

12

WA1AUCF38N1037885

20/11/2021

13

WA1BAAFY2M2138202

20/11/2021

14

WA1EECF38N1016615

20/11/2021

15

WA1LJBF75ND003545

20/11/2021

16

WAUABCF53NA005548

20/11/2021

17

WAUW4GF52NN001023

20/11/2021

18

WA1BNAFY9L2035619

20/11/2021

19

WA1EECF31N1017976

20/11/2021

20

WA1EECF34N1031368

20/11/2021

21

WA12AAGE2NB009236

20/11/2021

22

WAUBBCF57NA003774

20/11/2021

23

WAUABCF50NA004714

20/11/2021

24

WA1DECF39N1033130

20/11/2021

25

WA15AAFY3N2004927

20/11/2021

26

WA1DECF37N1018142

20/11/2021

27

WA1EAAFY8N2005720

20/11/2021

28

WA1VAAF76HD026727

20/11/2021

29

WA1AAAFY8M2130598

20/11/2021

30

WA1LAAGE7NB009477

20/11/2021

31

WA124AFY3N2028932

20/11/2021

32

WA1AAAFY7M2116885

20/11/2021

33

WA1LAAGEXNB009070

20/11/2021

34

WA1EAAFY8N2026938

20/11/2021

35

WAUEJBFW7N7003192

20/11/2021

36

WA1B4AFYXN2039040

20/11/2021

37

WAUH3DGY4NA005116

20/11/2021

38

WA1EECF32N1007912

20/11/2021

39

WA1AAAGE9NB005552

20/11/2021

40

WA1EECF39N1019149

20/11/2021

41

WA1C4AFYXM2091111

20/11/2021

42

WA1EECF3XN1018723

20/11/2021

43

WAUCBCF50NA006083

20/11/2021

44

WA1EAAFY5N2016741

20/11/2021

45

WAUABCF57NA006055

20/11/2021

46

WA1AAAGE7NB010961

20/11/2021

47

WAUY4GF57NN001379

20/11/2021

48

WA1AAAGE0NB010834

20/11/2021

49

WA1B4AFY6N2031260

20/11/2021

50

WA1EAAFYXN2026116

20/11/2021

51

WA1LAAGE5NB011759

20/11/2021

52

WA1LJBF71ND001209

20/11/2021

53

WA1EECF32N1006260

20/11/2021

54

WAUDFAF28MN076537

20/11/2021

55

WA1C4AFY5N2031898

20/11/2021

56

WAU8EAF87MN004902

20/11/2021

57

WAUY4GF50NN001319

20/11/2021

58

WA1VWBF79ND000089

20/11/2021

59

WUAPCBF25NN900741

20/11/2021

60

WA1AAAFY2M2137871

20/11/2021

61

WAUH3DGYXNA004004

20/11/2021

62

WA1EECF38N1029722

20/11/2021

63

WA14AAFY0N2000863

20/11/2021

64

WAUABCF54MA030859

20/11/2021

65

WA1AJBF72ND004157

20/11/2021

66

WA1EAAFY4N2016407

20/11/2021

67

WA1AUCF3XN1041145

20/11/2021

68

WA1GAAFY1N2031035

20/11/2021

69

WAU8SAF89MN027459

20/11/2021

70

WAUV2BF28NN014031

20/11/2021

71

WA1EECF37N1032093

20/11/2021

72

WAUY4GF5XNN001358

20/11/2021

73

WUA1CBF22NN901254

20/11/2021

74

WA1DECF38N1031496

20/11/2021

75

WA1FAAFY1N2004137

20/11/2021

76

WA1CVBF11ND006359

20/11/2021

77

WA1EECF30N1040987

20/11/2021

78

WA1EECF33N1035217

20/11/2021

79

WA1GAAFYXN2031857

20/11/2021

80

WA1LAAGE5NB009381

20/11/2021

81

WA1LAAGE9NB009240

20/11/2021

82

WA1VXBF71ND003694

20/11/2021

83

WUAKBAFX3N7900373

20/11/2021

84

WA1EECF35N1030732

20/11/2021

85

WA1AUCF35N1041408

20/11/2021

86

WA1GAAFY6N2029264

20/11/2021

87

WAUFJBFW1N7004424

20/11/2021

88

WA1EECF38N1030255

20/11/2021

89

WAUY4GF55NN001168

20/11/2021

90

WA1B4AFY1N2035491

20/11/2021

91

WA1EECF39N1033164

20/11/2021

92

WA1GAAFY4N2029215

20/11/2021

93

WA12AAGE2MB015097

20/11/2021

94

WAUBBAF41NN003483

20/11/2021

95

WA1AUCF3XN1037595

20/11/2021

96

WA1EECF35N1033307

20/11/2021

97

WA1GAAFY5N2016232

20/11/2021

98

WA1LXBF78ND003554

20/11/2021

99

WAUABCF53NA005629

20/11/2021

100

WAUCBCF55MA062504

20/11/2021

101

WAUE3BF22NN013812

20/11/2021

CR 11.20 to 1.9

Book1.xlsm

F

G

H

I

J

2

Data

Date

Difference

3

WA1EAAFY9N2046096

31/12/2021

0

4

WA1DECF36N1057997

31/12/2021

15

5

WA1DECF35N1047025

31/12/2021

0

6

WAUABAF49NN004604

31/12/2021

0

7

WA1CWBF10ND009296

31/12/2021

0

8

WA1EABFY5N2047235

31/12/2021

0

9

WA1FAAFY1N2043486

31/12/2021

0

10

WAUGUDGY6NA018247

31/12/2021

0

11

WAUHUDGY6NA018348

31/12/2021

0

12

WAUW4GF55NN001677

31/12/2021

0

13

WA1C4AFYXN2041519

31/12/2021

0

14

WAU72BF24NN013175

31/12/2021

0

15

WA1EECF30N1044117

31/12/2021

0

16

WA1AAAGE9NB011142

31/12/2021

0

17

WAUCBAF41NN002421

31/12/2021

0

18

WA1DECF37N1044238

31/12/2021

0

19

WA1A4AFY3N2037805

31/12/2021

0

20

WAUFACF56NA013474

31/12/2021

0

21

WAUDAAF4XNN004188

31/12/2021

0

22

WA1AAAGE3NB013453

31/12/2021

0

23

WAUEAAF47NN004072

31/12/2021

0

24

WA1LAAGE3NB013090

31/12/2021

0

25

WA1AAAGE4NB012943

31/12/2021

0

26

WA1FVBF16ND006466

31/12/2021

0

27

WAUYAGF53NN001727

31/12/2021

0

28

WA1EAAFY2N2046781

31/12/2021

0

29

WA1EECF37N1049749

31/12/2021

0

30

WA1EECF31N1051707

31/12/2021

0

31

WA17AAF40NA006601

31/12/2021

0

32

WA1LAAGE6NB011446

31/12/2021

0

33

WU1ARBF10ND009029

31/12/2021

0

34

WA1DECF31N1054067

31/12/2021

0

35

WA1DECF30N1053587

31/12/2021

0

36

WA1EAAFY9N2044123

31/12/2021

0

37

WAUPFBF24NN018560

31/12/2021

0

38

WAUAUDGY6NA018142

31/12/2021

0

39

WA15AAFY7N2045304

31/12/2021

0

40

WA1LXBF78ND005630

31/12/2021

0

41

WA124AFY5N2038930

31/12/2021

0

42

WA124AFY7N2042414

31/12/2021

0

43

WA134AFY7N2037833

31/12/2021

0

44

WAUEAAF48NN004078

31/12/2021

0

45

WAUP4AF59NA010827

31/12/2021

0

46

WA1LXBF73ND005843

31/12/2021

0

47

WA1FAAFY9N2045860

31/12/2021

0

48

WAUAHBFW1N7902379

31/12/2021

0

49

WA1AUCF31N1020426

31/12/2021

0

50

WA1EAAFYXN2044437

31/12/2021

0

51

WA1EAAFY5N2045110

31/12/2021

0

52

WAUV2BF23NN008833

31/12/2021

0

53

WAUW4GF59NN001732

31/12/2021

0

54

WA1LJBF72ND004345

31/12/2021

0

55

WAUABCF55NA011240

31/12/2021

0

56

WA1FVBF10ND006544

31/12/2021

0

57

WA1LXBF7XND005256

31/12/2021

0

58

WA1EECF35N1051757

31/12/2021

0

59

WAUCBCF5XNA011338

31/12/2021

0

60

WA1B4AFY0N2039404

31/12/2021

0

61

WAUABCF50NA006222

31/12/2021

0

62

WA1C4AFY8N2040109

31/12/2021

0

63

WAUAUDGYXNA030102

31/12/2021

0

64

WA12ABGE3NB008487

31/12/2021

0

65

WA11AAGE2NB013850

31/12/2021

0

66

WA1DECF31N1021702

31/12/2021

0

67

WA1LJBF7XND005291

31/12/2021

0

68

WA1DECF38N1029120

31/12/2021

0

69

WAU92BF20NN014235

31/12/2021

0

70

WAUFJBFW5N7004149

31/12/2021

0

71

WA1EECF36N1053324

31/12/2021

0

72

WA1LXBF7XND005807

31/12/2021

0

73

WAUEAAF40NN004074

31/12/2021

0

74

WA1EECF3XN1051222

31/12/2021

0

75

WA124AFY4N2042645

31/12/2021

0

76

WA15AAFY6N2046816

31/12/2021

0

77

WA1B4AFY3N2044905

31/12/2021

0

78

WA1BVBF13ND009839

31/12/2021

0

79

WA1EECF3XN1045128

31/12/2021

0

80

WA1EECF39N1045038

31/12/2021

0

81

WA1VXBF70ND005727

31/12/2021

0

82

WAUBBAF45NN003714

31/12/2021

0

83

WA1FAAFY6N2043001

31/12/2021

0

84

WAUFACF51NA012880

31/12/2021

0

85

WA1B4AFY1N2040545

31/12/2021

0

86

WAUCJBFW6N7001585

31/12/2021

0

87

WA1DECF34N1043919

31/12/2021

0

88

WA1LJBF78ND006262

31/12/2021

0

89

WA1CWBF15ND008354

31/12/2021

0

90

WA1EAAFY7N2044895

31/12/2021

0

91

WAUFJBFWXN7003790

31/12/2021

0

92

WA1B4AFY8N2040414

31/12/2021

0

93

WA1VABGE7NB015505

31/12/2021

0

94

WA1LXBF77ND005568

31/12/2021

0

95

WA1LJBF75ND005425

31/12/2021

0

96

WAUCBCF51NA011373

31/12/2021

0

97

WA1DECF39N1032057

31/12/2021

0

98

WA1FAAFYXN2043213

31/12/2021

0

99

WAUD3BF2XNN016596

31/12/2021

0

100

WA1LABGE1NB007259

31/12/2021

0

101

WA1EECF3XN1048353

31/12/2021

0

102

WAUEAAF49NN002551

31/12/2021

0

KPI Data - MY22 only

288enzo · Jan 13, 2022

JEC said:
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 · Jan 13, 2022

BSALV said:

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

288enzo · Jan 13, 2022

BSALV said:

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

A

B

C

D

E

F

G

H

I

J

K

L

1

Data

Date

Data

Date

Difference

Column1

Column2

2

WA1EAAFY5N2027089

11/20/2021

WA1EAAFY9N2046096

12/31/2021

4

44565

WA1EAAFY9N2046096 |44565|19398

3

WAUC4CF51NA004434

11/20/2021

WA1DECF36N1057997

12/31/2021

3

44564

WA1DECF36N1057997 |44564|19093

4

WAUCBCF56NA008887

11/20/2021

WA1DECF35N1047025

12/31/2021

5

44566

WA1DECF35N1047025 |44566|19966

5

WA1EAAFY4N2032087

11/20/2021

WAUABAF49NN004604

12/31/2021

6

WA1EECF33N1015033

11/20/2021

WA1CWBF10ND009296

12/31/2021

7

WA1GAAFY0N2034265

11/20/2021

WA1EABFY5N2047235

12/31/2021

4

44565

WA1EABFY5N2047235 |44565|19442

8

WA1LAAGE4NB006701

11/20/2021

WA1FAAFY1N2043486

12/31/2021

9

WAU92BF2XNN014310

11/20/2021

WAUGUDGY6NA018247

12/31/2021

10

WAU72BF20NN014680

11/20/2021

WAUHUDGY6NA018348

12/31/2021

4

44565

WAUHUDGY6NA018348 |44565|19372

11

WA1GAAFY0N2029101

11/20/2021

WAUW4GF55NN001677

12/31/2021

12

WA1AUCF38N1037885

11/20/2021

WA1C4AFYXN2041519

12/31/2021

13

WA1BAAFY2M2138202

11/20/2021

WAU72BF24NN013175

12/31/2021

7

44568

WAU72BF24NN013175 |44568|20255

14

WA1EECF38N1016615

11/20/2021

WA1EECF30N1044117

12/31/2021

15

WA1LJBF75ND003545

11/20/2021

WA1AAAGE9NB011142

12/31/2021

7

44568

WA1AAAGE9NB011142 |44568|20129

16

WAUABCF53NA005548

11/20/2021

WAUCBAF41NN002421

12/31/2021

17

WAUW4GF52NN001023

11/20/2021

WA1DECF37N1044238

12/31/2021

5

44566

WA1DECF37N1044238 |44566|20042

18

WA1BNAFY9L2035619

11/20/2021

WA1A4AFY3N2037805

12/31/2021

4

44565

WA1A4AFY3N2037805 |44565|19628

19

WA1EECF31N1017976

11/20/2021

WAUFACF56NA013474

12/31/2021

4

44565

WAUFACF56NA013474 |44565|19243

20

WA1EECF34N1031368

11/20/2021

WAUDAAF4XNN004188

12/31/2021

21

WA12AAGE2NB009236

11/20/2021

WA1AAAGE3NB013453

12/31/2021

22

WAUBBCF57NA003774

11/20/2021

WAUEAAF47NN004072

12/31/2021

3

44564

WAUEAAF47NN004072 |44564|19023

23

WAUABCF50NA004714

11/20/2021

WA1LAAGE3NB013090

12/31/2021

24

WA1DECF39N1033130

11/20/2021

WA1AAAGE4NB012943

12/31/2021

3

44564

WA1AAAGE4NB012943 |44564|18997

25

WA15AAFY3N2004927

11/20/2021

WA1FVBF16ND006466

12/31/2021

3

44564

WA1FVBF16ND006466 |44564|19044

26

WA1DECF37N1018142

11/20/2021

WAUYAGF53NN001727

12/31/2021

27

WA1EAAFY8N2005720

11/20/2021

WA1EAAFY2N2046781

12/31/2021

28

WA1VAAF76HD026727

11/20/2021

WA1EECF37N1049749

12/31/2021

5

44566

WA1EECF37N1049749 |44566|19876

29

WA1AAAFY8M2130598

11/20/2021

WA1EECF31N1051707

12/31/2021

30

WA1LAAGE7NB009477

11/20/2021

WA17AAF40NA006601

12/31/2021

7

44568

WA17AAF40NA006601 |44568|20340

KPI Data - MY22 ONLY

288enzo · Jan 13, 2022

bebo021999 said:
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
A B
1 Data Date
2 WA1EAAFY5N2027089 20/11/2021
3 WA1DECF36N1057997 20/11/2021
4 WA1DECF36N1057997 15/01/2022
5 WA1DECF36N1057997 16/03/2021
6 WA1EECF33N1015033 20/11/2021
7 WA1DECF36N1057997 15/08/2021
8 WA1LAAGE4NB006701 20/11/2021
9 WAU92BF2XNN014310 20/11/2021
10 WAU72BF20NN014680 20/11/2021
11 WA1GAAFY0N2029101 20/11/2021
12 WA1AUCF38N1037885 20/11/2021
13 WA1BAAFY2M2138202 20/11/2021
14 WA1EECF38N1016615 20/11/2021
15 WA1LJBF75ND003545 20/11/2021
16 WAUABCF53NA005548 20/11/2021
17 WAUW4GF52NN001023 20/11/2021
18 WA1BNAFY9L2035619 20/11/2021
19 WA1EECF31N1017976 20/11/2021
20 WA1EECF34N1031368 20/11/2021
21 WA12AAGE2NB009236 20/11/2021
22 WAUBBCF57NA003774 20/11/2021
23 WAUABCF50NA004714 20/11/2021
24 WA1DECF39N1033130 20/11/2021
25 WA15AAFY3N2004927 20/11/2021
26 WA1DECF37N1018142 20/11/2021
27 WA1EAAFY8N2005720 20/11/2021
28 WA1VAAF76HD026727 20/11/2021
29 WA1AAAFY8M2130598 20/11/2021
30 WA1LAAGE7NB009477 20/11/2021
31 WA124AFY3N2028932 20/11/2021
32 WA1AAAFY7M2116885 20/11/2021
33 WA1LAAGEXNB009070 20/11/2021
34 WA1EAAFY8N2026938 20/11/2021
35 WAUEJBFW7N7003192 20/11/2021
36 WA1B4AFYXN2039040 20/11/2021
37 WAUH3DGY4NA005116 20/11/2021
38 WA1EECF32N1007912 20/11/2021
39 WA1AAAGE9NB005552 20/11/2021
40 WA1EECF39N1019149 20/11/2021
41 WA1C4AFYXM2091111 20/11/2021
42 WA1EECF3XN1018723 20/11/2021
43 WAUCBCF50NA006083 20/11/2021
44 WA1EAAFY5N2016741 20/11/2021
45 WAUABCF57NA006055 20/11/2021
46 WA1AAAGE7NB010961 20/11/2021
47 WAUY4GF57NN001379 20/11/2021
48 WA1AAAGE0NB010834 20/11/2021
49 WA1B4AFY6N2031260 20/11/2021
50 WA1EAAFYXN2026116 20/11/2021
51 WA1LAAGE5NB011759 20/11/2021
52 WA1LJBF71ND001209 20/11/2021
53 WA1EECF32N1006260 20/11/2021
54 WAUDFAF28MN076537 20/11/2021
55 WA1C4AFY5N2031898 20/11/2021
56 WAU8EAF87MN004902 20/11/2021
57 WAUY4GF50NN001319 20/11/2021
58 WA1VWBF79ND000089 20/11/2021
59 WUAPCBF25NN900741 20/11/2021
60 WA1AAAFY2M2137871 20/11/2021
61 WAUH3DGYXNA004004 20/11/2021
62 WA1EECF38N1029722 20/11/2021
63 WA14AAFY0N2000863 20/11/2021
64 WAUABCF54MA030859 20/11/2021
65 WA1AJBF72ND004157 20/11/2021
66 WA1EAAFY4N2016407 20/11/2021
67 WA1AUCF3XN1041145 20/11/2021
68 WA1GAAFY1N2031035 20/11/2021
69 WAU8SAF89MN027459 20/11/2021
70 WAUV2BF28NN014031 20/11/2021
71 WA1EECF37N1032093 20/11/2021
72 WAUY4GF5XNN001358 20/11/2021
73 WUA1CBF22NN901254 20/11/2021
74 WA1DECF38N1031496 20/11/2021
75 WA1FAAFY1N2004137 20/11/2021
76 WA1CVBF11ND006359 20/11/2021
77 WA1EECF30N1040987 20/11/2021
78 WA1EECF33N1035217 20/11/2021
79 WA1GAAFYXN2031857 20/11/2021
80 WA1LAAGE5NB009381 20/11/2021
81 WA1LAAGE9NB009240 20/11/2021
82 WA1VXBF71ND003694 20/11/2021
83 WUAKBAFX3N7900373 20/11/2021
84 WA1EECF35N1030732 20/11/2021
85 WA1AUCF35N1041408 20/11/2021
86 WA1GAAFY6N2029264 20/11/2021
87 WAUFJBFW1N7004424 20/11/2021
88 WA1EECF38N1030255 20/11/2021
89 WAUY4GF55NN001168 20/11/2021
90 WA1B4AFY1N2035491 20/11/2021
91 WA1EECF39N1033164 20/11/2021
92 WA1GAAFY4N2029215 20/11/2021
93 WA12AAGE2MB015097 20/11/2021
94 WAUBBAF41NN003483 20/11/2021
95 WA1AUCF3XN1037595 20/11/2021
96 WA1EECF35N1033307 20/11/2021
97 WA1GAAFY5N2016232 20/11/2021
98 WA1LXBF78ND003554 20/11/2021
99 WAUABCF53NA005629 20/11/2021
100 WAUCBCF55MA062504 20/11/2021
101 WAUE3BF22NN013812 20/11/2021
CR 11.20 to 1.9

Book1.xlsm
F G H I J
2 Data Date Difference
3 WA1EAAFY9N2046096 31/12/2021 0
4 WA1DECF36N1057997 31/12/2021 15
5 WA1DECF35N1047025 31/12/2021 0
6 WAUABAF49NN004604 31/12/2021 0
7 WA1CWBF10ND009296 31/12/2021 0
8 WA1EABFY5N2047235 31/12/2021 0
9 WA1FAAFY1N2043486 31/12/2021 0
10 WAUGUDGY6NA018247 31/12/2021 0
11 WAUHUDGY6NA018348 31/12/2021 0
12 WAUW4GF55NN001677 31/12/2021 0
13 WA1C4AFYXN2041519 31/12/2021 0
14 WAU72BF24NN013175 31/12/2021 0
15 WA1EECF30N1044117 31/12/2021 0
16 WA1AAAGE9NB011142 31/12/2021 0
17 WAUCBAF41NN002421 31/12/2021 0
18 WA1DECF37N1044238 31/12/2021 0
19 WA1A4AFY3N2037805 31/12/2021 0
20 WAUFACF56NA013474 31/12/2021 0
21 WAUDAAF4XNN004188 31/12/2021 0
22 WA1AAAGE3NB013453 31/12/2021 0
23 WAUEAAF47NN004072 31/12/2021 0
24 WA1LAAGE3NB013090 31/12/2021 0
25 WA1AAAGE4NB012943 31/12/2021 0
26 WA1FVBF16ND006466 31/12/2021 0
27 WAUYAGF53NN001727 31/12/2021 0
28 WA1EAAFY2N2046781 31/12/2021 0
29 WA1EECF37N1049749 31/12/2021 0
30 WA1EECF31N1051707 31/12/2021 0
31 WA17AAF40NA006601 31/12/2021 0
32 WA1LAAGE6NB011446 31/12/2021 0
33 WU1ARBF10ND009029 31/12/2021 0
34 WA1DECF31N1054067 31/12/2021 0
35 WA1DECF30N1053587 31/12/2021 0
36 WA1EAAFY9N2044123 31/12/2021 0
37 WAUPFBF24NN018560 31/12/2021 0
38 WAUAUDGY6NA018142 31/12/2021 0
39 WA15AAFY7N2045304 31/12/2021 0
40 WA1LXBF78ND005630 31/12/2021 0
41 WA124AFY5N2038930 31/12/2021 0
42 WA124AFY7N2042414 31/12/2021 0
43 WA134AFY7N2037833 31/12/2021 0
44 WAUEAAF48NN004078 31/12/2021 0
45 WAUP4AF59NA010827 31/12/2021 0
46 WA1LXBF73ND005843 31/12/2021 0
47 WA1FAAFY9N2045860 31/12/2021 0
48 WAUAHBFW1N7902379 31/12/2021 0
49 WA1AUCF31N1020426 31/12/2021 0
50 WA1EAAFYXN2044437 31/12/2021 0
51 WA1EAAFY5N2045110 31/12/2021 0
52 WAUV2BF23NN008833 31/12/2021 0
53 WAUW4GF59NN001732 31/12/2021 0
54 WA1LJBF72ND004345 31/12/2021 0
55 WAUABCF55NA011240 31/12/2021 0
56 WA1FVBF10ND006544 31/12/2021 0
57 WA1LXBF7XND005256 31/12/2021 0
58 WA1EECF35N1051757 31/12/2021 0
59 WAUCBCF5XNA011338 31/12/2021 0
60 WA1B4AFY0N2039404 31/12/2021 0
61 WAUABCF50NA006222 31/12/2021 0
62 WA1C4AFY8N2040109 31/12/2021 0
63 WAUAUDGYXNA030102 31/12/2021 0
64 WA12ABGE3NB008487 31/12/2021 0
65 WA11AAGE2NB013850 31/12/2021 0
66 WA1DECF31N1021702 31/12/2021 0
67 WA1LJBF7XND005291 31/12/2021 0
68 WA1DECF38N1029120 31/12/2021 0
69 WAU92BF20NN014235 31/12/2021 0
70 WAUFJBFW5N7004149 31/12/2021 0
71 WA1EECF36N1053324 31/12/2021 0
72 WA1LXBF7XND005807 31/12/2021 0
73 WAUEAAF40NN004074 31/12/2021 0
74 WA1EECF3XN1051222 31/12/2021 0
75 WA124AFY4N2042645 31/12/2021 0
76 WA15AAFY6N2046816 31/12/2021 0
77 WA1B4AFY3N2044905 31/12/2021 0
78 WA1BVBF13ND009839 31/12/2021 0
79 WA1EECF3XN1045128 31/12/2021 0
80 WA1EECF39N1045038 31/12/2021 0
81 WA1VXBF70ND005727 31/12/2021 0
82 WAUBBAF45NN003714 31/12/2021 0
83 WA1FAAFY6N2043001 31/12/2021 0
84 WAUFACF51NA012880 31/12/2021 0
85 WA1B4AFY1N2040545 31/12/2021 0
86 WAUCJBFW6N7001585 31/12/2021 0
87 WA1DECF34N1043919 31/12/2021 0
88 WA1LJBF78ND006262 31/12/2021 0
89 WA1CWBF15ND008354 31/12/2021 0
90 WA1EAAFY7N2044895 31/12/2021 0
91 WAUFJBFWXN7003790 31/12/2021 0
92 WA1B4AFY8N2040414 31/12/2021 0
93 WA1VABGE7NB015505 31/12/2021 0
94 WA1LXBF77ND005568 31/12/2021 0
95 WA1LJBF75ND005425 31/12/2021 0
96 WAUCBCF51NA011373 31/12/2021 0
97 WA1DECF39N1032057 31/12/2021 0
98 WA1FAAFYXN2043213 31/12/2021 0
99 WAUD3BF2XNN016596 31/12/2021 0
100 WA1LABGE1NB007259 31/12/2021 0
101 WA1EECF3XN1048353 31/12/2021 0
102 WAUEAAF49NN002551 31/12/2021 0
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 · Jan 13, 2022

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

lookup match return closest to x date

288enzo

Well-known Member

Fluff

MrExcel MVP, Moderator

Excel Facts

288enzo

Well-known Member

BSALV

Banned user

JEC

Well-known Member

bebo021999

Well-known Member

288enzo

Well-known Member

288enzo

Well-known Member

288enzo

Well-known Member

288enzo

Well-known Member

JEC

Well-known Member

Similar threads

Forum statistics

Share this page

lookup match return closest to x date

Well-known Member

MrExcel MVP, Moderator

Excel Facts

Well-known Member

Banned user

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock