Is there a way to take data from one sheet and transpose it into another sheet.

Question

Good morning,&nbsp;I am trying to take the data that I can copy and paste into excel in this format from a HR system.&nbsp;&nbsp;And place the name and the start and end times into a sheet that appears like the below.&nbsp; I want the name to auto populate and likewise if a start time from the top table is 0700 and end time is 1900 I want the formulas to identify that and fill it in with the letter A. Then remain blank if they are not working at that time.&nbsp;&nbsp;&nbsp;

peterbartholomew1 · Answer

Nightstrand&nbsp;=LET(
    clockin,  IFERROR(MOD(TEXTBEFORE(clockData, "-"), 1), "Off"),
    clockout, MOD(TEXTAFTER(clockData, "-"), 1),
    adjusted, N(clockout &lt; clockin),
    IFERROR(
        HSTACK(
            name,
            clockin,
            (timeline &gt;= clockin) * (timeline &lt;= clockout + adjusted)
        ),
        ""
    )
)giving

peterbartholomew1 · Answer

Nightstrand&nbsp;

nightstrand · Answer

PeterBartholomew1&nbsp;&nbsp;Thank you for looking at this for me, I'll put your code into my spreadsheet and see if I can get it working&nbsp;

Forum Discussion

Is there a way to take data from one sheet and transpose it into another sheet.

3 Replies

Resources