Google sheets copy cell from specific columns

Question

I am using google form to generate a google sheet. One of the questions I have has a list of names of employees. Whenever a person leaves or I add someone the columns of employee names starts over. So now I have 5 different rows with the same persons name as the header. I am trying to make a formula to copy the numbers from the previous columns into the last. I tried this:

=ArrayFormula(IFS(ROW(C:C)=1, "Employee 1 Hours", B:B="", "",BS:BS <>"",BS:BS,CT:CT <>"",CT:CT,DU:DU <>"",DU:DU ,EU:EU <>"",EU:EU ,FV:FV <>"",FV:FV , ISBLANK(BS:BS) + ISBLANK(CT:CT) + ISBLANK(DU:DU), "", ISBLANK(EU:EU), "", ISBLANK(FV:FV), "")) which works but then I have to go through each person and adjust which columns they are in. Can I have it search either multiple rows for "Employee 1" header and copy a specific row in the column like BS:CS then search CS:DU and so on or is there a way to search for "Employee 1" and skip occurrences where the cell is empty and move on to the next occurrence of "Employee 1"" until it finds a cell that is not empty?

Example spreadsheet below:

example spreadsheet

player0 player0 · Accepted Answer · 2021-08-25T21:37:09

based on your image... if you transpose your whole sheet you can use query to filter down the employee and rotate it back like:

=TRANSPOSE(QUERY(TRANSPOSE(A:H), 
 "where Col1 matches 'Date|Job' 
     or Col1 starts with 'Employee 1 ' "))

Google sheets copy cell from specific columns

1 Answers