Search code examples
excelvbareporttransform

How do I transform multiple columns to a stacked column set of three in VBA?

I have data (see image one) of twelve months columned by Date-Jan, Day, Hours,/ Date-Feb, Day, Hours,/etc... for a total of 36 columns.

Data

I am trying to transform that sheet into a new sheet report style and its should look stacked (see image 2) it should have four columns by Name (of the employee), Date, Day, Hours.

report style

Few things to Note:

It has to be a reference - so when I change the hours in sheet1 in automatically updates in sheet2. NO COPY PASTE. (so like example(=b2)) (no need to be reversed) .

If I try to reiterate by every three columns, keep in mind that every month has a different amount of days and we don't want empty rows.

I was thinking of vlookup or the index function, but cant seem to make it work

Solution

  • Developping a single dynamic formula reference (MS 365)

    "It has to be a reference - so when I change the hours in sheet1 in automatically updates in sheet2"

    The pre-determined fixed structure lets you a way out to get the wanted dynamic reference. The "escape route" is simply to

    • recalculate the date values (col 2-Date and 3-Day) as numeric sequence by a formula instead of trying to refer to the original dates,
    • and to include all needed column results as first step in something like
       =CHOOSE({1,2,3,4},Employee,dt,dt,hours)

    As following step you need to enclose all needed column parameters into sort of a formula container (LET()) defining all column inputs, partially by reference to named cells, which could be for example

    • Employee .. e.g. "Bob Smith")
    • StartDate .. e.g. 4/1 2021 (equalling here 44291)
    • StartYear .. e.g. 2021
    • StartMonth.. e.g. 4

    The LET function (available in MS 365) allows to do this in a structured way avoiding some redundancies as well.

        =LET(data,Sheet1!$C$6:$AL$36,dt,SEQUENCE(366,1,StartDate),hours,INDEX(data,DAY(dt),(MONTH(dt)+(YEAR(dt)-StartYear)*12-StartMonth)*3+3),CHOOSE({1,2,3,4},Employee,dt,dt,hours))

    By entering this formula into any target cell (e.g. in Sheet2) you get a dynamic spill range of four columns with automatic display of changes of original hours.

    Hint: I leave it to you to refine the Let formula considering leap years, too.

    To get eventually the right report layout for the calculated date sequences (just numbers), you have to format the 2nd and 3rd output columns with the wanted date formats, e.g. "'m\/d" and "[$-409]ddd".

    Overview of formula parts

    which includes line feeds for better readibility

     =LET(
      data,  Sheet1!$C$6:$AL$36,
      dt,    SEQUENCE(366,1,StartDate),
      hours, INDEX(data,DAY(dt),(MONTH(dt)+(YEAR(dt)-StartYear)*12-StartMonth)*3+3),
      CHOOSE({1,2,3,4},Employee,dt,dt,hours)
     )