Is there a formula or function ?

[wanalearn]

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

[#2]

@wanalearn 

If I understand your problem correctly, this can be achieved with the FILTER() function. See the attached file.

[#3]

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

[#4]

where do I have to put it in the formula bar on all column's

[#5]

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.

[#6]

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"

[#7]

@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

[#8]

@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  

[#9]

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

[#10]

@mtarler 

thanks for your help

when I just copied & past your formula I got on some columns #ref errors 

when I modified it as followed: 

in line 4 i deleted "rate"

in line 9  I entered "sick" and " salary"

I got the #ref error,

see attached 

[#11]

@wanalearn my apologies.  I got ahead of myself and anticipated you might change the number of columns on the input side and tried to throw that option in also but didn't fix the algorithm later.  Here is the updated formula and attached sheet with the correction:

=LET(
    in, table_or_range,
    HeaderList, EarningsList,
    InTableHeaders, {"Names", "Earnings", "Hours", "Amount"},
    useManual, TRUE,
    HeaderPicked, IF(
        NOT(ISOMITTED(EarningsList)),
        EarningsList,
        IF(
            useManual,
            {
                "Reg", "Regular";
                "OT", "Overtime";
                "Holiday", "Holiday";
                "Bonus", "Bonus";
                "Sick", "Sick";
                "Salary", "Salary";
                "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, columnS(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),
            Ccount*rows(headerOrder) + 1,
            LAMBDA(r, c,
                IF(
                    c = 1,
                    XLOOKUP(r, lines, names),
                    FILTER(
                        INDEX(in, , MOD(c -2, ccount) + 3),
                        (lines = r) * (INDEX(in, , 2) = INDEX(headerOrder, QUOTIENT(c -2, Ccount)+1)),
                        ""
                    )
                )
            )
        )
    ),
    o
)

[#12]

@mtarler 

please see attached 

look in name Manager what did I do wrong ?

[#13 · Accepted solution]

@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