Hello All,
I'd like some help formatting this (Tbl-A)(Loaded via Power Query)
into this (Tbl-B)
What I used to populate Tbl-B Columns (All the same Index/Match formula logic):
Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Problems:
Please let me know the best way of going about this whether in Power Query or excel.
Many thanks for any help with this
I'd like some help formatting this (Tbl-A)(Loaded via Power Query)
into this (Tbl-B)
What I used to populate Tbl-B Columns (All the same Index/Match formula logic):
Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Problems:
- If someone fails to clock in or out, the entire column is thrown off
- The In and out times are not linked to the Employee ID so they are essentially listed in order of appearance. The red on Tbl-B indicates the start of error (Matching Tbl-A)
Please let me know the best way of going about this whether in Power Query or excel.
Many thanks for any help with this