1

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

2
  • Welcome to Web Applications! Take the site Tour and review How to Ask. While links can be helpful, your question should include the information needed to answer. Edit the question to include some minimal sample data and your expected result. Convert copied cells/tables from Sheets to proper markdown format. Tools like the Markdown Table Generator can make this easier. Include relevant formulas describing that did/didn't work including any errors you encountered.
    – Blindspots
    Aug 18, 2023 at 0:56
  • "single formula to repeat values in cell C3:C100, 12 times in column AO" Is this an XY problem? Aug 20, 2023 at 8:47

2 Answers 2

0

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.
3
  • Ummm.... @Blind Spots, INCREDIBLE. So a couple nuances putting to work. For the Repeated Values - Need a carriage return, or it will spread out to rows - For the Amount, there weren't instructions, so similar to the property name, I did the amount range (X6:X106),(Y6:Y106)... Need to iron out sequence and complete, but conceptually all there. See column AU for result.
    – BMCF
    Aug 18, 2023 at 20:01
  • @BMCF FYI I didn't visit the link but answered the question based on the details you included in the question. "For the Repeated Values - Need a carriage return, or it will spread out to rows" I don't understand what this means. Why would it spread out to rows? Do you mean that you need a CR in the formula? The source data? I'm missing something. Please also edit your question to include any additional relevant info. Happy to improve my answer if I can.
    – Blindspots
    Aug 18, 2023 at 20:11
  • @BMCF I just took a quick look at the your sheet and it seems like this might be an example of an XY Problem Seems like a lot of duplication and separate formulas that should be combined. If you edit the question to include what you actually want to do there is likely a simpler solution with fewer moving parts. It seems like C:E are the source data with some hardcoded values in row 3 and row 4 and everything else is calculated from there.
    – Blindspots
    Aug 18, 2023 at 21:13
0

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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.