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

     

     

12 Replies

    • 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  

         

Resources