Running a "Last Seen Report" using the data in 1 Tab and viewing it in another Tab

[Wilkesy13]

Hi there,

I fear this may be a hugely technical request but have no doubt in the ability of the amazing people on here.

Question:
I have a spreadsheet that I use for monitoring assets, the assets are kept in one location but out of the pool of assets there are always some that are being used and so can be weeks or months until they are checked. I have the assets listed in a Tab in column A (for discussion purposes let's say 1-50). I have then grouped 5 columns (C-G, H-L, M-Q etc.) under specific dates/days of the week when certain checks are completed and headed as follows "Checked," "Checked By," "Ticket Ref:," "Condition" and "Fault." You can see how it works in the example below and all of the dates that the checks are performed are listed in Row 2.

The "Checked By," "Condition" & "Fault" cells all have drop down lists. When an option is chosen from the "Checked By" column it turns the "Checked" column green and places a 1 in the cell.

What I'd really like to be able to do, in another Tab, is be able to see when an asset was last checked. I have absolutely no idea if this is possible as I have only BASIC excel knowledge but if anyone has an idea or solution I will be very pleased to hear it.

I hope I have explained adequately but in anyone's quest for help, if you have questions please let me know.

For anyone that tackles this mighty challenge I thank you sincerely in advance.

 

[Joe4]

Last Seen Report.xlsx Here's the link, I'm hoping this works!

Yes, that was very helpful. I see what is going on.
You have Conditional Formatting to hide some zero values, and I left the optional "Approximate Match" argumnent for the MATCH function off.
We need to put that in there to work on your data, so the formula should be:

=INDEX(Sheet1!B$2:MO$2,MATCH("1",Sheet1!B4:MO4,0))

 

[Wilkesy13]

Yes, that was very helpful. I see what is going on.
You have Conditional Formatting to hide some zero values, and I left the optional "Approximate Match" argumnent for the MATCH function off.
We need to put that in there to work on your data, so the formula should be:

=INDEX(Sheet1!B$2:MO$2,MATCH("1",Sheet1!B4:MO4,0))

So close. This is now doing the opposite. If there is more than one "1" in the "Checked" column of a Row it will show the date of the earliest as below.

 

[Joe4]

OK, that gets quite tricky, and I am not sure to write the formula to return the last match (there is a way to do it, I am sure, but it is a very advanced formula).
I would use VBA to do this myself. Here is how I would do it. Just run the following code on your data, and it should populate the values:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
    
    Application.ScreenUpdating = False
    
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
    
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
    
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column A on data sheet
    For r2 = 2 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "A")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "A") = lu Then
'               Find last column with data on data sheet
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "B") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

End Sub

 

[Wilkesy13]

OK, that gets quite tricky, and I am not sure to write the formula to return the last match (there is a way to do it, I am sure, but it is a very advanced formula).
I would use VBA to do this myself. Here is how I would do it. Just run the following code on your data, and it should populate the values:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
   
    Application.ScreenUpdating = False
   
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
   
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
   
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all cells in column A on data sheet
    For r2 = 2 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "A")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "A") = lu Then
'               Find last column with data on data sheet
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "B") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

End Sub

Wow this is another level!

I copied it into the "View Code" option after right clicking on Sheet2 but can't get it to work. I am very excel basic. Is this the correct process?

I have shared the latest file in Drop Box and will add the link below.

Thanks for all of your help on this!

Dropbox

www.dropbox.com

 

[Joe4]

Wow this is another level!

I copied it into the "View Code" option after right clicking on Sheet2 but can't get it to work. I am very excel basic. Is this the correct process?

I have shared the latest file in Drop Box and will add the link below.

Thanks for all of your help on this!

Dropbox

www.dropbox.com

It sounds like you placed it in a Sheet module.
You should add a new, General module (i.e. "Module1") in VBA, and add it there.
See here for how to do that:

 

[Wilkesy13]

It sounds like you placed it in a Sheet module.
You should add a new, General module (i.e. "Module1") in VBA, and add it there.
See here for how to do that:

It feels so close but it will only populate on the one row and only when I force the code to run.

The video was useful and helped me add a general module but got stuck after that. Here is where I got to

Dropbox

www.dropbox.com

 

[Joe4]

It looks like in the later data samples you posted, you have duplicate values in column A on both sheets, and then do not end in the same place.
I am not really sure what that means, and I had not accounted for this.

 

[Wilkesy13]

It looks like in the later data samples you posted, you have duplicate values in column A on both sheets, and then do not end in the same place.
I am not really sure what that means, and I had not accounted for this.

Ahh ok, would it be better to use the data from column B instead of A as this data should be unique
I have added Column B from Sheet1 to Column B in Sheet2. There shouldn't be any duplicates in this field of data

Dropbox

www.dropbox.com

 

[Joe4]

I cannot view that file right now, but see if these modifications to my previous code work:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
    
    Application.ScreenUpdating = False
    
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
    
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
    
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column A on data sheet
    For r2 = 4 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "A")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "B") = lu Then
'               Find last column with data on data sheet
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "B") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

End Sub

 

[Wilkesy13]

I cannot view that file right now, but see if these modifications to my previous code work:

Sub MyPopulateData()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim lu As String
    Dim lr1 As Long
    Dim r1 As Long
    Dim lr2 As Long
    Dim r2 As Long
    Dim lc1 As Long
    Dim c1 As Long
   
    Application.ScreenUpdating = False
   
'   Set name of sheet where data resides
    Set sh1 = Sheets("Sheet1")
   
'   Set name of sheet where writing results
    Set sh2 = Sheets("Sheet2")
   
'   Find last row with data on data sheet
    lr1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last row with data in column A on data sheet
    lr2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all cells in column A on data sheet
    For r2 = 4 To lr2
'       Get value to lookup
        lu = sh2.Cells(r2, "A")
'       Find lookup value on data sheet
        For r1 = 4 To lr1
            If sh1.Cells(r1, "B") = lu Then
'               Find last column with data on data sheet
                lc1 = sh1.Cells(r1, Columns.Count).End(xlToLeft).Column
'               Loop through all columns backwards
                For c1 = lc1 To 3 Step -1
'                   Find last value equal to "1"
                    If sh1.Cells(r1, c1) = "1" Then
'                       Populate results sheet with date
                        sh2.Cells(r2, "B") = sh1.Cells(2, c1)
                        Exit For
                    End If
                Next c1
            End If
        Next r1
    Next r2

    Application.ScreenUpdating = True

End Sub

Thanks for Trying Joe,

I can't tell you how much I appreciate your efforts however, it doesn't seem to be doing anything.

I added a Module and copied your code into it but it doesn't seem to be doing anything. I am no doubt doing something wrong. I have tried running the code and also closing it and reopening it but still nothing.

The link I added earlier has Column B duplicated the same as Sheet1 into Sheet2 and again don't know if this will work better as there'll be no duplicates. Your knowledge is invaluable so if you are still happy to assist I will wait for your suggestions.