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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

BSALV

Active Member
Joined
Oct 31, 2010
Messages
275
Office Version
  1. 365
  2. 2013
  3. 2007
still MS2016, otherwise with filter etc ?
There were no matches, so I made some myself
forum.xlsb
ABCDEFGH
1DataDataDifference
2WA1EAAFY5N202708920/11/2021WA1EAAFY9N204609631/12/202141
3WAUC4CF51NA00443420/11/2021WA1DECF36N105799731/12/202112
4WAUCBCF56NA00888720/11/2021WA1DECF35N104702531/12/202141
5WA1EAAFY4N203208720/11/2021WAUABAF49NN00460431/12/202141
6WA1EECF33N101503320/11/2021WA1CWBF10ND00929631/12/202141
7WA1GAAFY0N203426520/11/2021WA1EABFY5N204723531/12/202141
8WA1LAAGE4NB00670120/11/2021WA1FAAFY1N204348631/12/2021
Blad3
Cell Formulas
RangeFormula
G2:G7G2=MIN(FILTER(ABS($B$2:$B$101-F2),$A$2:$A$101=E2,"-"))
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
still MS2016, otherwise with filter etc ?
There were no matches, so I made some myself
forum.xlsb
ABCDEFGH
1DataDataDifference
2WA1EAAFY5N202708920/11/2021WA1EAAFY9N204609631/12/202141
3WAUC4CF51NA00443420/11/2021WA1DECF36N105799731/12/202112
4WAUCBCF56NA00888720/11/2021WA1DECF35N104702531/12/202141
5WA1EAAFY4N203208720/11/2021WAUABAF49NN00460431/12/202141
6WA1EECF33N101503320/11/2021WA1CWBF10ND00929631/12/202141
7WA1GAAFY0N203426520/11/2021WA1EABFY5N204723531/12/202141
8WA1LAAGE4NB00670120/11/2021WA1FAAFY1N204348631/12/2021
Blad3
Cell Formulas
RangeFormula
G2:G7G2=MIN(FILTER(ABS($B$2:$B$101-F2),$A$2:$A$101=E2,"-"))
I was so excited, but I get the message "That function isn't valid".
 

BSALV

Active Member
Joined
Oct 31, 2010
Messages
275
Office Version
  1. 365
  2. 2013
  3. 2007
spillformula (=CTRL+SHIFT+Enter) but with 21.000 rows is VBA perhaps a better idea.Is this a once in a while check or permanent ?
Map1
ABCDEFG
1DataKolom1DataKolom1Difference
2WA1EAAFY5N202708911/20/21WA1EAAFY9N204609612/31/2141
3WAUC4CF51NA00443411/20/21WA1DECF36N105799712/31/211000000000
4WA1EAAFY9N204609611/20/21WA1DECF35N104702512/31/211000000000
5WA1EAAFY4N203208711/20/21WAUABAF49NN00460412/31/211000000000
6WA1EECF33N101503311/20/21WA1CWBF10ND00929612/31/211000000000
7WA1GAAFY0N203426511/20/21WA1EABFY5N204723512/31/211000000000
8WA1LAAGE4NB00670111/20/21WA1FAAFY1N204348612/31/211000000000
Blad1
Cell Formulas
RangeFormula
G2:G8G2=MIN(IF(Tabel1[Data]=[@Data],ABS(Tabel1[Kolom1]-[@Kolom1]),1000000000))
 

288enzo

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

ADVERTISEMENT

spillformula (=CTRL+SHIFT+Enter) but with 21.000 rows is VBA perhaps a better idea.Is this a once in a while check or permanent ?
Map1
ABCDEFG
1DataKolom1DataKolom1Difference
2WA1EAAFY5N202708911/20/21WA1EAAFY9N204609612/31/2141
3WAUC4CF51NA00443411/20/21WA1DECF36N105799712/31/211000000000
4WA1EAAFY9N204609611/20/21WA1DECF35N104702512/31/211000000000
5WA1EAAFY4N203208711/20/21WAUABAF49NN00460412/31/211000000000
6WA1EECF33N101503311/20/21WA1CWBF10ND00929612/31/211000000000
7WA1GAAFY0N203426511/20/21WA1EABFY5N204723512/31/211000000000
8WA1LAAGE4NB00670111/20/21WA1FAAFY1N204348612/31/211000000000
Blad1
Cell Formulas
RangeFormula
G2:G8G2=MIN(IF(Tabel1[Data]=[@Data],ABS(Tabel1[Kolom1]-[@Kolom1]),1000000000))
The data would only be checked once a week.

I don't quite follow your formula. One thing, every data point in your right table will be found at the very least once in the left table. If each data point only matched up once, this would be fairly straight forward. My problem is there could be 15 matches for one data point in the left table and I need to find the one that has a date closest to the date of the original data point in the right table.

I tried something similar, each cell came back with 1000000000, with the exception of one cell that came back with a zero.

Book1.xlsx
ABCDEFG
1DataDateDataDateDifference
2WA1EAAFY5N202708911/20/2021WA1EAAFY9N204609612/31/20211000000000
3WAUC4CF51NA00443411/20/2021WA1DECF36N105799712/31/20211000000000
4WAUCBCF56NA00888711/20/2021WA1DECF35N104702512/31/20211000000000
5WA1EAAFY4N203208711/20/2021WAUABAF49NN00460412/31/20211000000000
6WA1EECF33N101503311/20/2021WA1CWBF10ND00929612/31/20211000000000
7WA1GAAFY0N203426511/20/2021WA1EABFY5N204723512/31/20211000000000
8WA1LAAGE4NB00670111/20/2021WA1FAAFY1N204348612/31/20211000000000
9WAU92BF2XNN01431011/20/2021WAUGUDGY6NA01824712/31/20211000000000
10WAU72BF20NN01468011/20/2021WAUHUDGY6NA01834812/31/20211000000000
11WA1GAAFY0N202910111/20/2021WAUW4GF55NN00167712/31/20211000000000
12WA1AUCF38N103788511/20/2021WA1C4AFYXN204151912/31/20211000000000
13WA1BAAFY2M213820211/20/2021WAU72BF24NN01317512/31/20211000000000
14WA1EECF38N101661511/20/2021WA1EECF30N104411712/31/20211000000000
15WA1LJBF75ND00354511/20/2021WA1AAAGE9NB01114212/31/20211000000000
16WAUABCF53NA00554811/20/2021WAUCBAF41NN00242112/31/20211000000000
17WAUW4GF52NN00102311/20/2021WA1DECF37N104423812/31/20211000000000
18WA1BNAFY9L203561911/20/2021WA1A4AFY3N203780512/31/20211000000000
19WA1EECF31N101797611/20/2021WAUFACF56NA01347412/31/20211000000000
20WA1EECF34N103136811/20/2021WAUDAAF4XNN00418812/31/20211000000000
21WA12AAGE2NB00923611/20/2021WA1AAAGE3NB01345312/31/20211000000000
22WAUBBCF57NA00377411/20/2021WAUEAAF47NN00407212/31/20211000000000
23WAUABCF50NA00471411/20/2021WA1LAAGE3NB01309012/31/20211000000000
24WA1DECF39N103313011/20/2021WA1AAAGE4NB01294312/31/20211000000000
KPI Data - MY22 ONLY
Cell Formulas
RangeFormula
G2:G24G2=MIN(IF(Table1[Data]=[Data],ABS(Table1[Date]-[Date]),1000000000))
 

BSALV

Active Member
Joined
Oct 31, 2010
Messages
275
Office Version
  1. 365
  2. 2013
  3. 2007
VBA Code:
Sub compairing()
     Dim x(), Res()

     Set dictarr = CreateObject("scripting.dictionary")         'dictionary
     a = Range("TBL_1").Value2                                  'read 1st table
     For i = 1 To UBound(a)                                     'loop through data
          If Not dictarr.exists(a(i, 1)) Then                   'if key doesn't exist
               ReDim x(0): x(0) = a(i, 2)                       'make array and assign 1st element
               dictarr(a(i, 1)) = x                             'write to dictionary
          Else
               it = dictarr(a(i, 1))                            'read former content for that key
               x = it                                           'copy to another array
               ReDim Preserve x(UBound(x) + 1)                  'redim +1 with conservation of content
               x(UBound(x)) = a(i, 2)                           'add new element
               dictarr(a(i, 1)) = x                             'write back to dictionary
          End If
     Next

     Set C = Range("TBL_2").Resize(, 2)                         'range 2nd table
     a = C.Value2                                               'read 2nd table
     ReDim Res(1 To UBound(a), 1 To 2)                          'make resultat array
     For i = 1 To UBound(a)                                     'loop through data
          If dictarr.exists(a(i, 1)) Then                       'if key exists
               it = dictarr(a(i, 1))                            'read content
               If UBound(it) = 0 Then                           'if only 1 date = easy
                    Res(i, 1) = Abs(it(0) - a(i, 2))            'difference
                    Res(i, 2) = it(0)                           'date
               Else
                    x = Application.Sort(Application.Transpose(it), 1)     'transpose and sort (ascending) array
                    r = Application.Match(a(i, 2), x, 1)        'match date
                    If IsNumeric(r) Then r = 1                  'if wanted date is before 1st date, then take 1st date
                    If r < UBound(x) Then
                         If Abs(x(r, 1) - a(i, 2)) > Abs(x(r + 1, 1) - a(i, 2)) Then r = r + 1     'choose smallest difference between date and preceding and following date
                    End If
                    Res(i, 1) = Abs(x(r, 1) - a(i, 2)): Res(i, 2) = x(r, 1)     'write difference and date
               End If
          End If
     Next

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

288enzo

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

ADVERTISEMENT

VBA Code:
Sub compairing()
     Dim x(), Res()

     Set dictarr = CreateObject("scripting.dictionary")         'dictionary
     a = Range("TBL_1").Value2                                  'read 1st table
     For i = 1 To UBound(a)                                     'loop through data
          If Not dictarr.exists(a(i, 1)) Then                   'if key doesn't exist
               ReDim x(0): x(0) = a(i, 2)                       'make array and assign 1st element
               dictarr(a(i, 1)) = x                             'write to dictionary
          Else
               it = dictarr(a(i, 1))                            'read former content for that key
               x = it                                           'copy to another array
               ReDim Preserve x(UBound(x) + 1)                  'redim +1 with conservation of content
               x(UBound(x)) = a(i, 2)                           'add new element
               dictarr(a(i, 1)) = x                             'write back to dictionary
          End If
     Next

     Set C = Range("TBL_2").Resize(, 2)                         'range 2nd table
     a = C.Value2                                               'read 2nd table
     ReDim Res(1 To UBound(a), 1 To 2)                          'make resultat array
     For i = 1 To UBound(a)                                     'loop through data
          If dictarr.exists(a(i, 1)) Then                       'if key exists
               it = dictarr(a(i, 1))                            'read content
               If UBound(it) = 0 Then                           'if only 1 date = easy
                    Res(i, 1) = Abs(it(0) - a(i, 2))            'difference
                    Res(i, 2) = it(0)                           'date
               Else
                    x = Application.Sort(Application.Transpose(it), 1)     'transpose and sort (ascending) array
                    r = Application.Match(a(i, 2), x, 1)        'match date
                    If IsNumeric(r) Then r = 1                  'if wanted date is before 1st date, then take 1st date
                    If r < UBound(x) Then
                         If Abs(x(r, 1) - a(i, 2)) > Abs(x(r + 1, 1) - a(i, 2)) Then r = r + 1     'choose smallest difference between date and preceding and following date
                    End If
                    Res(i, 1) = Abs(x(r, 1) - a(i, 2)): Res(i, 2) = x(r, 1)     'write difference and date
               End If
          End If
     Next

     C.Offset(, C.Columns.Count).Resize(, 2).Value = Res        'write result to table (3rd and eventually 4th column if 2 !!!
End Sub
Thank you very much for taking the time to write all that with explanations, it will certainly help when I dig in to better understand.

For now, I see you are using "TBL_" for the table name, is that correct? If so, I renamed my tables to match, but I'm unsure which is which. I named the table on the left, the one that consists of 21,000+ rows TBL_1 and the other TBL_2. When doing so I received an error - Run-time error '438': Object doesn't support this property or method

This bit of code is highlighted - x = Application.Sort(Application.Transpose(it), 1) 'transpose and sort (ascending) array

When I swapped the table names I didn't receive an error, however the "difference" calculation is on the left table and not the right table where I need it to be.

Thank you again.
 

BSALV

Active Member
Joined
Oct 31, 2010
Messages
275
Office Version
  1. 365
  2. 2013
  3. 2007
it was perhaps easier to change the names in VBA then to change the name of the tables, but indeed the left one is tBL_1 and the other Tbl_2.
When writing the result-array to Tbl_2, you can choose for 1 or 2 columns, just by changing the 2 in Resize(, 2) into 1.
Do you use "option explicit" on top of your module ?
I try figuring out what your problem is, perhaps 2016, i have 365.


Does this work, take an empty sheet and it 'll add 100*3 random values, and then sort them by the 1st, then the 2nd and finally the 3rd column.
Do you receive errors ?

VBA Code:
Sub test()
     Dim arr, arr1, i
     With Range("A1").Resize(100, 3)
          .Formula = "=RAND()"
          arr = .Value
          For i = 1 To 3
               arr1 = Application.Sort(arr, i)
               .Offset(, i * 4 + 1).Value = arr1
          Next
     End With
End Sub
 
Last edited:

288enzo

Active Member
Joined
Feb 8, 2009
Messages
470
Office Version
  1. 2016
Platform
  1. Windows
it was perhaps easier to change the names in VBA then to change the name of the tables, but indeed the left one is tBL_1 and the other Tbl_2.
When writing the result-array to Tbl_2, you can choose for 1 or 2 columns, just by changing the 2 in Resize(, 2) into 1.
Do you use "option explicit" on top of your module ?
I try figuring out what your problem is, perhaps 2016, i have 365.
Added option explicit. I have 2016, wish I had 365.

Compile error: Variable not defined

highlighted is "dictarr" - Set dictarr = CreateObject("scripting.dictionary")
 

BSALV

Active Member
Joined
Oct 31, 2010
Messages
275
Office Version
  1. 365
  2. 2013
  3. 2007
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 ?
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,466
Messages
5,764,488
Members
425,219
Latest member
datdanigg

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