Payroll table creation challenge - Fill rows with various text requirements and repetition

Question

In the "Payroll export" worksheet, I need to take the data in columns C through AL, and convert it to what I've built columns AN through AR.

I have used an Array formula to paste the property, names, GL account, which are all the same, then another paste for the individual cost per property. This needs to be repeated 12 times. The reason for this is so that I can build the pivot tables.

My question / challenges are:

• Is there a single formula that would allow me to specify whatever is in cell X3, repeat in cells AN6:AN100, and Y3 repeat in cells AN101:AN200, and Z3 repeat in cells AN201:A300?

• Is there a single formula to repeat values in cell C3:C100, 12 times in column AO?

Payroll table challenge

https://docs.google.com/spreadsheets/d/1PaMPM-2hx-87lz4E3_ynDZ5l0WDbNfLOPCo8uYuJyY8/edit?usp=sharing

Answer 1

Stack Repeated Values Arrays

=ARRAYFORMULA({ 
   IFERROR(AN6:AN100/0, X3);
   IFERROR(AN101:AN200/0, Y3);
   IFERROR(AN201:A300/0, Z3) 
 })

• Divide every value in a range by 0 to return an array of #DIV/0! errors.
• IFERROR replaces each error with the value in the referenced cell.

Multi-Line Formulas

• I add spaces between arguments and split long formulas across multiple lines for stylistic reasons.
• In Sheets I find them easier to review and edit.
• Here it helps reduce the need to scroll back and forth to see the complete formula in a code block.
• There is no reason the spaces can't be eliminated and the formula entered as a single line.
  For example, the repeated values formula above could also be:

=ARRAYFORMULA({IFERROR(AN6:AN100/0,X3);IFERROR(AN101:AN200/0,Y3);IFERROR(AN201:A300/0,Z3)})

Stack Array 12 Times

=TOCOL(ARRAYFORMULA(
   IF(ROW(1:12), 
     TOROW(C3:C100))))

• For each row numbered from 1 to 12, return a row populated with the values in C3:C100
• Uses TOCOL to convert the 12x98 (HxW) array to a single column with 1,176 rows.

Answer 2

It sounds like you would like to unpivot the "wide" data so that it is "tall" and row-oriented, like this:

Full name	GL account	Property	Total cost	Property	Percentage	Dollars
Noah Olivia	Manager	ST	$211	ST	100%	$211
Liam Emma	Renovation	OPP	$509	OPP	100%	$509
Oliver Amelia	Repairs & Maintenance	NE	$730	NE	100%	$730
Elijah Sophia	Renovation	NE	$767	NE	100%	$767
Mateo Ava	Repairs & Maintenance	CS	$416	CS	100%	$416
Lucas Charlotte	Renovation	RW	$640	RW	100%	$640
Leo Isabella	Manager	PV	$705	PV	100%	$705
Ezra Mia	Renovation	NE	$443	NE	100%	$443
Levi Luna	Renovation	OPP	$573	OPP	100%	$573
James Aurora	Renovation	Custom	$359	NE	33%	$120
James Aurora	Renovation	Custom	$359	GL	33%	$120
James Aurora	Renovation	Custom	$359	RW	33%	$120
...	...	...	...	...	...	...

The first four columns are repeated, but instead of "12 times" they are repeated only as many times as required to include all data in the Percentage and Dollars columns.

To do that, choose Insert > Sheet and put this formula in cell A1 of the new sheet:

=let( 
  header, 'Payroll export'!A3:AL3, 
  data, filter('Payroll export'!A6:AL, len('Payroll export'!C6:C)), 

  cols_, lambda(d, choosecols(d, 
    sequence(1, columns('Payroll export'!C3:F3), column('Payroll export'!C3)), 
    torow(
      vstack(
        sequence(1, columns('Payroll export'!H3:U3), column('Payroll export'!H3)), 
        sequence(1, columns('Payroll export'!X3:AK3), column('Payroll export'!X3)) 
      ), 
      0, true 
    ) 
  )), 

  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if(k,data,hstack(sequence(rows(data)),data)),f,if(k,k,1),g,numColsPerGroup,r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),h,hstack(s_(1,1,1,f),"Property","Percentage","Dollars"),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s_(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s_(r,c,1,g),if(single(z)="",y,vstack(y,hstack(x,s_(1,c,1,1),z)))))),vstack(a,b)))),if(k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  table, vstack(cols_(header), cols_(data)), 

  unpivot_(table, 4, 2) 
)

You can then re-pivot the data to get the reports you want. Use Insert > Pivot table or query() formulas like this:

=query( 
  A1:G, 
  "select B, sum(G) 
   where B is not null 
   group by B 
   pivot E 
   format sum(G) '$0.00' ", 
  1 
)

This example query() gives are simple report that looks like this:

GL account	CC	CS	FV	GA	GL	...
Accounting & Legal			$76.85		$76.85	...
Administrative	$38.56	$64.68	$237.33	$74.32	$208.41	...
Landscaping						...
Manager	$613.40	$440.20	$95.50	$789.40	$38.20	...
Renovation	$31.58	$202.91	$31.51	$31.51	$151.16	...
Repairs & Maintenance		$416.00		$429.00		...