Extracting data from one sheet to the next

[OX_2005]

I have a spread sheet that is being filled in via a Microsoft Forms sheet with Power automate. After the table is filled in from the Form I am trying to bring over from the "Data Input"(DI) tab to the "Data Process" (DP) tab the Employee ID Date and Start Time if if in column D of the DP tab is "No". Then on the DP tab I need it to search below the row the data was pulled from on the DI tab and find a row for that employee with a "Yes" in column D and add the End Time into the DP tab, but if it finds a "No" in column D before a "Yes" then just start a new line and leave that cell blank. I am not sure if this is something a little bit of formulas would work or if this would be best done in VBA. Any help would be appreciated.

 

[Rijnsent]

Hi OX,
As a starter, you could use the FILTER function, I simplified your main/input table to the name tbl and gave the columns a simpler name:

=FILTER(tbl,tbl[remove]="No","EMPTY")

-> as No is a start time, you probably only want to start with those (where No is the starttime).
Next, in a column next to that area, you could do:

=XLOOKUP(1,(tbl[time]>K2)*(tbl[id]=M2),tbl[time],"-",0)

-> (in K2 is the first timestamp of my FILTER, in M2 the first ID). So you're basically doing a XLOOKUP with 2 criteria: the ID should match and the timestamp should be greater than that line. That should give you the "next line of info" for that combo. So you can use that with some more formulas to state whether there is an end time or not.
Cheers,
Koen

 

[OX_2005]

I added the formulas but I am getting a value of 0 for everything and I got a message when entered.

=FILTER(DataInput,[Removed from Task Type]="No","EMPTY")

=XLOOKUP(1,([Completion time]>G2)*([Employee ID Number]=C2),[Completion time],"-",0)

 

[Rijnsent]

Hi OX, the FILTER formula needs to go in an empty space (not as a formula next to the row), as it will create a range of results (not just one cell). See e.g. this tutorial for the basics; FILTER Function