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

     

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 02, 2022

    wanalearn  Here is what I think you want maybe.  see attach

     

    =LET(in,Table1,
names,SCAN("",INDEX(in,,1),LAMBDA(prev,this,IF(this="",prev,this))),
lines,SCAN(0,INDEX(in,,2),LAMBDA(prev,this,IF(this="Regular",prev+1,prev))),
MAKEARRAY(MAX(lines),9,LAMBDA(r,c,IF(c<2,INDEX(FILTER(names,lines=r),1),IF(c<5,FILTER(INDEX(in,,c+1),(lines=r)*(INDEX(in,,2)="Regular")),IF(c<8,FILTER(INDEX(in,,c-2),(lines=r)*(INDEX(in,,2)="Overtime"),""),FILTER(INDEX(in,,2*c-13),(lines=r)*(INDEX(in,,2)="Total Earnings"),"")))))))

    a quick summary of what it does is "names" fills in the gaps based on last used name,

    "lines" just assigns a # based on each "set" of lines starting with "Regular" earnings

    then MAKEARRAY is used to FILTER from the original table the corresponding row and column to create the new table.

    • wanalearn's avatar
      wanalearn
      Brass Contributor
      Feb 21, 2023

      mtarler sorry for everyone I wasn't clear enough with my question, so I want to clarify here what 
      I asked


      in my first question, I had two problems 
      1)  I want to fill in names based on the last entered name  (let's call it "fill in names") 
      2) I had in one column earnings and on the rows "Regular" and "OT" and on the other column the values, I wanted the separate the Regular and OT into different columns (let's call it "convert rows to columns")

      @mataler answered it with one long formula

       However, I want  to modify it

      1)to use the "convert rows to columns" formula alone 

      2) which part of the formula do I have to change if  I have other text in the earnings columns like

      "holiday" "sick"  

       

      since a part of my job is to import payroll reports on pdf into Excel and to convert rows into columns it's very important to me to be able to modify it.

      see below I will comment on the other formula  

       
      • mtarler's avatar
        mtarler
        Silver Contributor
        Feb 26, 2023

        wanalearn  so I re-did the 1st formula to make it much more flexible with lots of optional input:

        =LET(
    in, table_or_range,
    HeaderList, EarningsList,
    InTableHeaders, {"Names","Earnings","Hours","Rate","Amount"},
    useManual, true,
    HeaderPicked, if( not(ISOMITTED(EarningsList)), 
                   EarningsList, 
                   if(useManual,
                      {"Reg","Regular";"OT","Overtime";"Holiday","Holiday";"Bonus","Bonus";"Tot","Total Earnings"},
                      let(L,UNIQUE(choosecols(in,,2)),hstack(L,L))
                      )
                   ),
    header, DROP(REDUCE("",take(Headerpicked,,1),LAMBDA(g,h,HSTACK(g,h&" "&drop(InTableHeaders,,2)))),,1),
    headerOrder, take(HeaderPicked,,-1),
    Ccount, rows(InTableHeaders)-2,
    names, SCAN("", INDEX(in, , 1), LAMBDA(prev, this, IF(this = "", prev, this))),
    lines, drop(vstack(1,SCAN(1, INDEX(in, , 2), LAMBDA(prev, this, IF(this = take(headerOrder,-1), prev + 1, prev)))),-1),
    o, vstack(hstack("Name",header), MAKEARRAY(
        MAX(lines),
        COLUMNS(header)+1,
        LAMBDA(r, c,
            IF(
                c =1,
                XLOOKUP(r, lines, names),
                FILTER(INDEX(in, , mod(c+1,3)+3 ), (lines = r) * (INDEX(in, , 2) = index(headerOrder,QUOTIENT(c+1,3))), "")
            )
        )
    )),
    o
)

        as noted before there is a LOT here but it is in a NAMED Function so you just have to call it with the Table or Range.  There is an OPTIONAL input for EarningsList that you can choose to enter OR just change the values on Line 9 above OR change UseManual on line 5 to False and it will automatically create a header for each Earnings Type found in column 2 (but it probably won't be in the order you might want.  IF you enter the EarningList you want it must be "Header name to use","Earnings Name in List"; ...  basically a paired list of output name comma input name and the output name is then also concatenated with the input column names like "Hours", "Rate" and "Amount".  If those change you can tweak them on line 4.

        Hope this works for you.

         

         

    • wanalearn's avatar
      wanalearn
      Brass Contributor
      Sep 02, 2022
      where do I have to put it in the formula bar on all column's
      • mtarler's avatar
        mtarler
        Silver Contributor
        Sep 02, 2022
        If you paste it into the formula bar, all the lines will paste in correctly and yes you need all the lines as they are all part of 1 formula (you'll get an error otherwise). The separate lines is just intended to make it a little easier to read/understand.

Resources