Need to organize first data sheet into the second with duplicates of each project for each date with a value.

Use a method that converts the weekly “matrix” layout into a simple list. Power Query is the most flexible for this.

Assume the first layout is in a sheet named WeekData, with headers in row 1 and projects in column A.

1. Turn the range into a table
   1. Select any cell in the first sheet.
   2. Press Ctrl+T and confirm “My table has headers”.
2. Load the table into Power Query
   1. Go to Data > Get Data > From Other Sources > Blank Query.
   2. In the formula bar, enter:

            =Excel.CurrentWorkbook()
      

   3. In the list, find the table created in step 1 and click the Table link in its Content column.
3. Unpivot the date columns
   1. In Power Query, select the PROJECT column.
   2. On the Transform tab, choose Unpivot Columns > Unpivot Other Columns.
      • This produces three columns: PROJECT, Attribute, and Value.
      • Attribute contains the date headers (3/1/2026, 3/2/2026, …).
      • Value contains the hours (1, 2, etc.).
4. Clean up the result
   1. Rename columns:
      • PROJECT → Project
      • Attribute → Date
      • Value → Regular Hours (or Overtime if this table is for overtime).
   2. Filter out blanks in Regular Hours so only rows with a value remain.
5. Load to the second sheet
   1. Select Home > Close & Load.
   2. Choose Table and the destination sheet that has the Project / Date / Regular Hours / Overtime headers.

The result is a list where each non‑blank cell from the original grid becomes one row with:

• Project = project number
• Date = column header date
• Regular Hours = the value from the grid

If overtime is tracked separately, repeat the same steps for the overtime table and load into the same destination sheet (append) with Overtime as the value column.

References:

• Combine data from multiple sheets

This is a classic data transformation / normalisation task. You can automate it using Power Query or Excel formulas.

Option A — Power Query (best for large datasets)

Select your first table → Data > From Table/Range

In Power Query, use Unpivot Columns to create a row for each project/date/value.

Rename columns to match the second sheet’s structure.

Close & Load into the second sheet.

Option B — Formula-based (if dates are in columns)

If your first sheet looks like:

| Project | 1 Jan | 2 Jan | 3 Jan | … |

Use this formula to flatten it:

Code

=LET(
    data, A2:D10,
    proj, TAKE(data,,1),
    dates, TAKE(A1:D1,,-1),
    vals, DROP(data,,1),
    HSTACK(
        INDEX(proj, SEQUENCE(ROWS(vals)*COLUMNS(vals),,1,1)),
        INDEX(dates, SEQUENCE(,COLUMNS(vals),1,1)),
        TOROW(vals,1)
    )
)

This produces a list of Project | Date | Value.