Formula to use to achieve the header into each row

Question

I need to convert some data in my Google sheet. Attached is the screenshot on how I currently have the data and how I am looking to format the data into.

Answer 1

assuming your data starts in cell Sheet1!A2 and extends indefinitely down and indefinitely over, try this:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Sheet1!A3:A&" "&Sheet1!B2:2&"|"&OFFSET(Sheet1!B3,,,9^9,9^9)),"|",0,0),"where Col2 is not null",0))

Answer 2

Derived from MattKing's answer, I've added transpose in conjunction with some manipulation on the concatenation part to follow the type of sorting your Required Data Format had. This should give you the same output you provided above.

Sample Data:

Formula:

=arrayformula(split(flatten(transpose(A2:A6&" "&B1:D1)&"|"&transpose(B2:D6)),"|"))

Where:

• A2:A6 is the range of your project names
• B1:D1 is the range of your headers
• B2:D6 is the range of your dates

Result:

EDIT:

If you are expecting blank cells in your dates such as the sample below (as pgSystemTester mentioned in the comments section):

You need to add query and exclude those rows that doesn't have dates.

=arrayformula(query({split(flatten(transpose(A2:A6&" "&B1:D1)&"|"&transpose(B2:D6)),"|")}, "where Col2 is not null"))

Answer 3

Just to give another option, and spurred on by this (likely repeat) question

=LET(x, $I$2:$L$4,
myrows, ROWS(x),
mycols, COLUMNS(x),
mycount, SEQUENCE(myrows*mycols),
car, $H$2:$H$4, color, $I$1:$L$1,
mylist, car&" "&color,
mycolumn, INDEX(mylist, CEILING(mycount/mycols,1), IF(MOD(mycount,mycols)=0, mycols,MOD(mycount,mycols))),
mydata, INDEX(x, CEILING(mycount/mycols,1), IF(MOD(mycount,mycols)=0, mycols,MOD(mycount,mycols))),
IF(SEQUENCE(1,2)=1, mycolumn, mydata))

Answer 4

You can see a sample sheet here, with several of answers illustrated on each tab.

Formula With No App Scripts

This is an update and includes a formula that was stolen from a different answer that appears to nail it. (Player() is officially the Jack Bauer of Spreadsheets... don't ask how he gets things done!).

=INDEX(QUERY(SPLIT(FLATTEN(IF(B2:E="",,A2:A&" "&B1:E1&"×"&B2:E)), "×"), 
 "where Col2 is not null"))

Make sure this is placed off to the right so that it doesn't spill into itself.

My Original App Scripts Solution

This is honestly not needed any more, but I worked on it for half an hour and maybe someone might find it helpful if they have trouble sleeping.

/**
 * Creates 2 Columns of Data
 *
 * @param {range} theRangeValues The Table Range.
 * @return The two columns of data with all combinations based on rows and columns
 * @customfunction
 */
function buildTwoColumnsData(theRangeValues) {
  var allValues = [];
  var i = 1;
  var cCell = theRangeValues[0][i];
  while (cCell != '' && i < theRangeValues[0].length) {
    var theEnd = cCell;
    var g = 1;
    var rCell = theRangeValues[g][0];
    while (rCell != '' && g < theRangeValues.length) {
      allValues.push([rCell + " " + theEnd, theRangeValues[g][i]])
      g++;
      if (g < theRangeValues.length) {
        rCell = theRangeValues[g][0];
      }
    }
    i++;
    if (i < theRangeValues[0].length) {
      cCell = theRangeValues[0][i];
    }
  }
  return allValues;
}