Simple example to clarify:
Continued from last post ( https://excelfox.com/forum/showthrea...ll=1#post19863
https://www.excelfox.com/forum/showt...ll=1#post21921 )
Explanation of the two Match bits
Rem 1
Code:
Rem 1 try to match the name & Activity & Sub-activity
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
Dim strSrch As String ': Let strSrch = Range("E2").Value & Range("F2").Value & Range("G2").Value ' This gives for example, "JohnA.1A.1.1"
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
Let strSrch = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim MtchRw As Long
Let MtchRw = Application.Match(strSrch, arrD1(), 0) ' this tries to match the correct row in Database1
This is fairly straight forward.
This is the array of name & Activity & Sub-activity
arrD1() =
NameActivitySub-activity
JohnA.1A.1.1
JohnA.1A.1.2
JohnA.2A.2.1
JohnA.2A.2.2
JohnA.3A.3.1
JohnA.3A.3.2
JohnA.4A.4.1
JohnA.4A.4.2
MarkA.1A.1.1
MarkA.1A.1.2
MarkA.2A.2.1
MarkA.2A.2.2
MarkA.3A.3.1
MarkA.3A.3.2
MarkA.4A.4.1
MarkA.4A.4.2
AnneA.1A.1.1
AnneA.1A.1.2
AnneA.2A.2.1
AnneA.2A.2.2
AnneA.3A.3.1
AnneA.3A.3.2
AnneA.4A.4.1
AnneA.4A.4.2
Now take for example the case of row 2 from worksheet database. I am looking to match this
John A.1 A.1.1
So that is obviously 2 – ( Match tells me the position along in the array that it finds what it is looking for, the second position in this case )
Rem 2 This is more tricky, and may not always work with different data. This initial solution may need to be re thought
This is the array of dates
arrDts() =
Name Activity Sub-activity 01-Nov-2022 44896 44927 44958 44986 45017 45047 45078
Now take for example from row 2, the date 30 November, 2022. Excel holds dates internally as numbers, and for the date 30 November, 2022 it has 44895.
I don’t have the number 44895 in my array. So this, Application.Match(DteV2, arrDts(), 0) , would give an error. It errors because it cannot find an exact match.
However we can use a 1 or -1 instead of the 0
If we do that it will not error if it finds something close to what its looking for. The exact rules governing how that works are not so clear. I have a feeling Microsoft have some inconstant documentation in this.
For the case of using a 1 , we must have the numbers its looking for in ascending order, which we have in the test data. I think then it tries to find the next smallest number.
Lets look again in detail at the test data and add some color highlighting to demo that
The array: ( from Database1)
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
Name |
Activity |
Sub-activity |
44866 |
44896 |
44927 |
44958 |
44986 |
45017 |
45047 |
45078 |
The 4 numbers I use from column D (from Database ) to get the results I want
If you follow the logic you can see that we will get the correct column numbers we want with the test data: We will get
4 4 5 7
But there are at least two issues to consider:
_1) This is relying on the date in column D in database being typically bigger than the corresponding date to be matched in Database1
_2) I am not sure how the Match always reacts to having a mixture of numbers and text in the array. It does not seem to cause a problem with the test data.
Bookmarks