Call Center - Login Logout Report

Please add to the workbook a manual mock-up of what you would like the template to do - layout, manually calculated results, etc.

OK - no rush.

In addition. I will attached the schedules.

In that template, one of the sheet in excel where I can say for example, a sheet which where I can copy and paste the data from "Agent Time card File" paste it to that sheet and when you click a button it will auto generate. and then a sheet where I can say, update or edit the status of the agent from unconfirmed to present, or late, or leave.

You didn't understand my request.

Is this data sensitive? If so, then you need to DESENSITISE it.

I asked you to MOCK UP what you want the template to do - what should it look like? Please provide a mock-up of what you want, where you want it.

There are multiple approaches to achieve the desired outcome, with one method utilizing helper columns. The resulting data is displayed in the 'After' sheet.

While it's feasible to accomplish the task without using helper columns, doing so often entails employing complex and resource-intensive formulas.

Alternatively, VBA (Visual Basic for Applications) could provide another avenue to accomplish the task.

I've provided a solution in the attachment, which utilizes formulas with helper columns. Please let me know if you prefer a VBA solution instead.

Thats OK.
Create another sheet, name "Output" to store the result
Try below code

PHP Code: 

Option Explicit
Sub test()
Dim lr&, i&, k&, rng, team$, agent$, st$, res()
With Sheets("Data")
    lr = .Cells(Rows.Count, "F").End(xlUp).Row ' last used row
    rng = .Range("A2:F" & lr).Value ' copy value range value into array
    ReDim res(1 To UBound(rng), 1 To 6)
    For i = 1 To UBound(rng)
        If rng(i, 1) <> "" Then ' column A <>""
            team = rng(i, 1)
        ElseIf rng(i, 2) <> "" Then ' column B <>""
            agent = rng(i, 2)
        ElseIf rng(i, 5) <> "" Then ' copy row into result array
            k = k + 1: res(k, 1) = team: res(k, 2) = agent: res(k, 3) = rng(i, 3)
            res(k, 4) = rng(i, 4): res(k, 5) = rng(i, 5): res(k, 6) = rng(i, 6)
        End If
    Next
End With
Sheets("Output").Activate
If k = 0 Then Exit Sub
Range("A2:F10000").ClearContents
Range("A2").Resize(k, 6).Value = res
End Sub 


I also would like to ask about the helper you have provided. whenever I paste another set of data, it won't show on the after sheet and it shows n/a. but i drop the formula as it is.

Make sure that you copy the formulas from the helper area on the right down, and you will see the results in the helper area.
If not, try to upload the new file with wrong result.

The query that you were given to use seems to be missing from that workbook. Instead there is a formula that was not mentioned in this thread so far.

So what do you want? Have you chosen not to use the query?

there are only 2 logins "Landayan"

Where? Which row(s)?

but supposed to have 3 based on the before sheet.

There are only two rows for that operative - explain why it should be three.

There are NO annotations in the workbook - no highlights, no signposting. Help us to help you.

EDIT: I see that you are using the solution that Bebo provided.

After receiving assistance, to grasp how to solve and address future arising issues, you should make an effort to understand how the formula works. The old VLOOKUP function at B2:

Please Login or Register  to view this content.

is easy to understand for expanding the range, for instance, to 10,000 rows:

Please Login or Register  to view this content.

is there a way to simplify the formula in the helper? like using index match or vlookup? or any other formula that is simply to understand? or if not, can you explain this formula?

IF($G4="","",LOOKUP(2,1/(A$2:A4<>""),A$2:A4))

IF($G4="","",

If G4 is blank, return a blank.

LOOKUP(2,1/(A$2:A4<>"")

Look for the last cell in the range that is not blank ...

,A$2:A4))

... and return what is in that cell.

That's all you need to know to be able to reuse this in many other contexts.

Don't know where the result should go, but I put the following in cell H2 of the "After" sheet of the file that is attached to post #20:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

This thread has been solved - please start a NEW thread with a suitable title.