Forum Discussion
Is there a formula or function ?
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 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.
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
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.
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
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 )