Match all Instances (VBA)

[hennesseeb]

I'm working on a file that needs to match up time stamps but from two different data sets. The only data that matches within those two data sets is a 10 digit number. Both sheets are sorted in ascending order so the time stamps will correspond when matched. There's nothing I can concantenate to create a helper column unfortunately. What I'm having trouble with is the match code returning the first match, then when the same digit code appears again, it doesn't pull the same time stamp again, but the second...third..etc. Also, the timestamps between the two data sets may/may not mach, and there should be a gap between them.

I know what needs to happen, but I'm not sure of the exact syntax of how to do it. I need the next match to start on the row below the last match. The number of matches is going to vary, so there's no set number. I'm not sure if this is possible.

Here's what my code looks like now:

r = Application.CountA(Sheets("Sheet1").Range("A:A"))

For i = 2 To r
     ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B:B"),0)
     If Not IsError(ilt) Then
           Sheets("Sheet1").Range("N" & i).Value = Sheets("Sheet2").Range("M" & ilt).Value
     End If
     ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt & ":1045876"),0)
Next

I know the syntax isn't right, and I normally don't have to match data like this, but it's the only way to get accurate reports. I hope this doesn't sound too confusing. I've searched around on the internet and I haven't found anything that has really helped me, so I'm reaching out to the board. I appreciate any help/guidance given. Thanks!

 

[tdh777]

Try this :

r   = Application.CountA(Sheets("Sheet1").Range("A:A"))
r2  = Application.CountA(Sheets("Sheet2").Range("B:B"))
ilt = 0

For i = 2 To r
     ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" [B]& ilt+1[/B] & ":B" [B]& r2[/B]),0)
     If Not IsError(ilt) Then
           Sheets("Sheet1").Range("N" & i).Value = Sheets("Sheet2").Range("M" & ilt).Value
     End If
     ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt & ":1045876"),0)
Next r

Now you are only searching from the row after the previous match downwards.

 

[hennesseeb]

I'm getting the first match returned on the first row, but as soon as it goes to the second row, it throwing Error 1004, "application-defined or object-defined error". The bold line of code is what's causing the error.

Sub test()

    r = Application.CountA(Sheets("Sheet1").Range("A:A"))
    n = Application.CountA(Sheets("Sheet2").Range("A:A"))
    ilt = 0
    
    For i = 2 To r
        ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt + 1 & ":B" & n), 0)
        If Not IsError(ilt) Then
            Sheets("Sheet1").Range("N" & i).Value = Sheets("Sheet2").Range("M" & ilt).Value
        End If
        
        [B]ilt = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt & ":1048576"), 0)[/B]

    Next

 

[tdh777]

Hi Again.
The error was always there in your original code as posted. I just copied and pasted that part.
You are missing the extra B, in your search range ie

[B]Range("B" & ilt & "[U]B[/U]:1048576")[/B]

However, now that I look more closely at your code, you don't need the BOLD line of code in your Macro AT ALL.

The adjusted Application.Match line at the top of the loop will find the next matching value starting at the row after the previous match and down.

Just get rid of the bold line (comment it out by preceding the line with a ' if you prefer) and you should get the result you want, but add in a little adjustment to ilt to reset it to 0 if there's no match.

For i = 2 to r
    ilt = [COLOR=#333333]Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt + 1 & ":B" & n), 0)
[/COLOR]    If IsError(ilt) Then
        ilt = 0      ' so that Match will work next time from top of list if no match found on a line.
    Else
        [COLOR=#333333]Sheets("Sheet1").Range("N" & i).Value = Sheets("Sheet2").Range("M" & ilt).Value
[/COLOR]    End If 
Next i

(Of course if you shared a few rows of sample or dummy data, it would be clearer to people reading this thread what's being discussed here.)

Cheers.

 

[hennesseeb]

I took the code you provided and ran it, but it's still not doing exactly what I need it to do, but progress has been make nonetheless. I have column headers on this data, so the latest code is actually returning the column header name in the match, which I don't want. I've played around with the value of "ilt" if error, but I'm still getting the first row for some matches.

What' it's doing now is still repeating some matches (timestamps in this case) and I don't need that to happen. Again both data sets are sorted in ascending order so the timestamps will be relative to one another. I'm going to provide some dummy data so you can better understand what needs to be done. I apologize as I should have provided this from the start but I was thinking I was clear in my instructions. Anyway, I just did a quick vlookup in the dummy data and that's basically how the macro is working. Based on the number, if it appears again down the list, I need a new match, not the previous one. See the link for the test data.





https://www.dropbox.com/s/o3s0jr8vb3deorl/test.xlsx?dl=0

 

[tdh777]

Hi Again Brendan,
We're getting closer.
The data you have shared shows that the data is sorted into time order, and not in "10-digit-number" order. The faulty macro code you started with, and the modification I gave you, assumed that the data was sorted in 10-digit number order. (I followed the lead in your code given that you just said "ascending order" in your post).

The simplest solution is to sort both sheets into 10-digit-number order (Column A/B) before doing the matching. (and if necessary back into Time order (Column M) after the matching is finished).

The original logic you wrote was as follows :

1. Get 10-digit-value for Row 2 in Sheet 1. (say "1234567890")
2. Find the first match for that 10-digit-Value in Column B in Sheet 2.
3. If there was a match, copy the corresponding Log-In time to Column N in Sheet 1 on the same row.
4. Now find the next instance of "1234567890" in Sheet 2.
5. Move to the next Row in Sheet 1 (Row 3).
6. Go to step 1 and continue.

If you read over those steps you will see that Step 4 was completely wasted...No point in finding the next instance of a 10-digit-number if we are about to move onto the next row in Sheet 1 anyway and it's a different value !!

And even if you had stayed on the SAME 10-digit-number, you were always searching from the TOP of Column B as your Main search (B:B) will always find the first value.

Also, when you copied my original post you changed the following line :

r2  = Application.CountA(Sheets("Sheet2").Range("B:B"))

to

n = Application.CountA(Sheets("Sheet2").Range("A:A"))

..which means that your current code will not work on the sample data you supplied (which has an Empty Column A)

Soooo... taking all these things into consideration, IF you sort both sheets into 10-digit-number order then the following slightly modified version of my original code will give you the result you asked for. [However please see my further comments below as to whether what you asked for is exactly what you need !]

Sub test()
    On Error Resume Next
    r = Application.CountA(Sheets("Sheet1").Range("A:A"))
    n = Application.CountA(Sheets("Sheet2").Range("B:B"))
    ilt = 0
    For i = 2 To r
        fnd = Application.Match(Sheets("Sheet1").Range("A" & i).Value, Sheets("Sheet2").Range("B" & ilt + 1 & ":B" & n), 0)
        If IsError(fnd) Then
            ilt = 0      ' so that Match will work next time from top of list if no match found on a line.
        Else
            ilt = ilt + fnd
            Sheets("Sheet1").Range("N" & i).Value = Sheets("Sheet2").Range("M" & ilt).Value
        End If
    Next i
End Sub

The output sample data (remaining sorted in 10-Digit-number order for convenience of understanding the discussion below) is located at https://www.dropbox.com/s/l636ki2vwom5kkl/test2.xls?dl=0

HOWEVER, I note from actually seeing example data and running this that there is NOT an exact match between the number of "starts" in Sheet 1 and the number of "Logins" in Sheet 2.

So for example for number 31125779 there are 10 entries ("starts") in Sheet 1 and 13 entries ("logins") in Sheet 2.

The code you originally wrote, the logic you described, and the same logic I have followed in the updated version will match the Nth matching version in Sheet 2.

In some cases the nth "Start time" (Sheet 1) is before the Nth login time, and in other cases it is after. So that makes it unclear as to the real-world relationship between the 2 times.

I suspect that what you want will be a more complex logic that finds the nearest matching login-time to the start time.... AS I look over your data there are some numbers where there are multiple "starts" for 1 "login" and in other cases , multiple "logins" for 1 "start".

So after you have looked over the results of the latest Macro, perhaps you will need to explain what the relationships between the entries really is, what you are trying to do with the data difference between Columns M and N in Sheet 1, and then we can refine the macro to get what you actually need, if the nth-matching time is not strictly what you are looking for.

Cheers,
Warren.

 

[hennesseeb]

Well, the code works if the 10-digit columns are sorting from smallest to largest, but I need both sheets sorted from oldest to newest in the time columns. The sequence of the numbers does not matter, but the login and start times do. So if the data is sorted from oldest to newest (columns M on both sheets) and then based on the 10 digit number on sheet 1, match the number to sheet 2 to pull the login time from sheet 2's matching row. The number will appear more than once, but like I stated before, I don't want the first matching row to show up for every number in the data set; they should be all different.

The number of matches will vary unfortunately, but it's important to keep it sorted by time. I'm trying to determine the amount of time between start time and login times. The closer the gap the better but negative time differences "shouldn't" happen but may, and those need to show up as well.

Hopefully all this makes sense now. I appreciate all the help.

<colgroup><col></colgroup><tbody>
</tbody>

 

[tdh777]

Hi Brendan,

I've already told you how to achieve the result you have requested, you need to :

1. Sort the data on both sheets first,

2. Run the Macro

3. Now if you then want the data back into Time order, just sort it back again !

If you want Steps 1 and 3 (Sorting) to happen automatically as part of the macro, simply :

1. Turn your Macro recorder on,
2. Sort both Sheets into 10-digit-number order.
3. Press the "stop recording" button.
4. Copy the resultant "sorting code" from the new macro into the top of the macro I gave you... (so the first thing it does is sort the sheets into 10-digit order).

Repeats Steps 1-4 this time sorting both sheets into column M (time-stamp order) and this time copy the Macro sorting code so that it goes at the END of the Macro I gave you (before "End Sub").

(I could do this for you but this way you will learn a new skill that you can use in the future... )


In your latest post, you also say things like :

I don't want the first matching row to show up for every number in the data set; they should be all different.

.

That is already done. Please refer to the sample results data from my last post. If you SORT your data and then run the macro you will also verify this.


Now, you still haven't really answered my question about the relationship between the time-stamps :

In some cases the nth "Start time" (Sheet 1) is before the Nth login time, and in other cases it is after. So that makes it unclear as to the real-world relationship between the 2 times.

1. What IS the real-world relationship between the "Start" and "Login" times ?

I said :

I suspect that what you want will be a more complex logic that finds the nearest matching login-time to the start time.... As I look over your data there are some numbers where there are multiple "starts" for 1 "login" and in other cases , multiple "logins" for 1 "start".

You said, as I suspected :

I'm trying to determine the amount of time between start time and login times.

..so what exactly is a "start" and what exactly is a "login" ?

And as I asked, how do you want to cope with the situation where there are "logins" with no "start" and "starts" with no "logins" ? Just having them show up as negative isn't enough... because you also have examples where there appears to be a "login" for a "start" but they don't always occur login first, then start...sometimes they are start first, then login.

So if you can explain exactly please by answering the following questions (in order) :

1. What is a Start, and what is a Login ? (Starting what, logging in to what) ?

2. Can a Start occur without any Login ?

3. Can a Login occur without any Start ?

4. Can a Start occur BEFORE a matching Login ?

5. Can a Login occur BEFORE a matching Start ?

6. What is the most common order of events ?

You say

The number of matches will vary unfortunately.

If the REASON for this is not made clear by answers to the above questions, please explain why.

If you could answer these qtns directly (one-by-one please) then we can get this finished and get you the actual result you want...

Cheers,
Warren.

 

[hennesseeb]

1. What is a Start, and what is a Login ?

A start is the beginning of a run. A login is when the person logs in to the job.

2. Can a Start occur without any Login ?

Yes

3. Can a Login occur without any Start ?

No there shouldn't be

4. Can a Start occur BEFORE a matching Login ?

Yes

5. Can a Login occur BEFORE a matching Start ?

Yes

6. What is the most common order of events ?

A run start should occur before a login.The way you have the macro now, sorting numbers from smallest to largest and then matching the login times and then resorting by the time columns is not giving me the desired results. The data set needs to stay sorted from oldest to newest by Column M (Sheet1) and Column M (Sheet2). I shouldn't have a run start time at 8:00am and a login at 3:45pm, because that's not right and it shouldn't be happening. Normally the login times should be within an hour (preferably less) but that's the point of this data set is to track the time gap between the two. I made a pivot from sheet 2 with the number as a filter and login times as the rows and put that on sheet one to check the results. I'm sure after answering your questions you have a better understanding of what needs to occur.

 

[hennesseeb]

Any help would be appreciated. This is a project I'm working on and I would like to have it done ASAP. Thanks!