Forum Discussion

wanalearn's avatar
wanalearn
Brass Contributor
Sep 02, 2022
Solved

Is there a formula or function ?

see attached    I want to have in column A ''Names'' and one column the  Regular values another column  Over Time values etc. if I do it, by creating a table the values don't match the correct names...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Mar 14, 2023

    wanalearn  It appears 2 things:

    a) it appears you have cases that have more than 1 instances of the same category in the same group.  I changed the formula to be a SUM() of the FILTER result to prevent that error

    b) some of the "values" are actually TEXT so I added a "--" in front of the FILTER to force numbers that look like text to be converted to numbers but that will break if actual text like "n/a" or "zero" or what not is in there.

    see attached

     

     

wanalearn's avatar
wanalearn
Brass Contributor
Feb 15, 2023

Can you please modify the formula, if I don't need to fill in the "names"
just i want separate columns for "OT" "Regular" Etc.?

and I also want to add if I have "Holiday" or "bonus"

mtarler's avatar
mtarler
Silver Contributor
Feb 15, 2023

wanalearn  so I'm not sure I understand but instead of combining all the 'related' rows into a single row with more columns here is a version that just explodes the existing data into many columns but the rows stay the same.  I have 2 options here: a) automatically detect all the columns to create or b) manually create the column headers. 

=LET(useManual, TRUE,
         MANheaderList, {"Reg","OT","Holiday","Bonus","Tot"},
         manHeaderOrder,{"Regular","Overtime","Holiday","Bonus","Total Earnings"},
         autoHeaderList, UNIQUE(Table1[Earnings]),
         header, DROP(REDUCE("",IF(useManual,MANheaderList,autoHeaderList),LAMBDA(g,h,HSTACK(g,h&" "&Table1[[#Headers],[Hours]:[Amount]]))),,1),
         autoheaderorder, autoHeaderList,
         headerOrder, IF(useManual,manHeaderOrder,autoheaderorder),
         blankrow,SUBSTITUTE(SEQUENCE(1,COLUMNS(header),1,0),1,""),
         out, IFERROR(REDUCE(header,SEQUENCE(ROWS(Table1)),LAMBDA(p,q,LET(o,XMATCH(TEXTBEFORE(INDEX(Table1[Earnings],q)," ",1,1,1)&"*",headerOrder,2)*3-2, z,VSTACK(p,DROP(HSTACK(CHOOSECOLS(blankrow,SEQUENCE(o)),CHOOSEROWS(Table1[[Hours]:[Amount]],q)),,1)),z))),""),
         out)

I realize this is rather large formula but as noted in the other thread it could get defined as a LAMBDA function but since you only need this in ONE cell, I really don't see the point.

As noted above, the first line 'useManual' will if TRUE use the following 2 lines to define the actual set of HEADERS and the order of the headers using the words it will find in the Earnings column.

This formula uses TABLE reference that are specific to this sheet so every case that has Table1...  needs to be changed to match the Table of interest.  So Table1[Earnings] is the column to find those header names,  and Table1[[Hours]:[Amount]] refers to the columns being moved based on those header names.  This formula is shown in columns Q and to the right in the attached sheet

Resources