User Profile
OliverScheurich
Gold Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, thank you for your detailed explanation. I understand the task and the formula in cell K7 returns the results. The formula must be filled down. In the screenshot i've hidden some rows and highlighted 3 unique combinations of "Supplier PO" and "SKU" for illustration. I've attached the sample file with 2 sheets with hidden and unhidden rows of the sample database. Regards, Oliver =IF( OR( AND(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0), AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "closed", IF( OR(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<>0, AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7<>0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "in progress", "") )Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, thank you for your detailed explanation. Unfortunately i didn't understand earlier what should be done. This formula spills the results. However the LAMBDA REDUCE VSTACK has some limitations therefore i've added basically the same formula in cell M7 of the attached sample file. The formula in M7 must be filled down and it doesn't have the limitations of the LAMBDA REDUCE VSTACK formula. =LET(_data,CHOOSECOLS(B7:AB66,1,3,6,9,21,27),DROP(REDUCE("",SEQUENCE(ROWS(_data)), LAMBDA(w,x,VSTACK(w,IF(INDEX(_data,x,1)="in","",LET(_rng,TAKE(_data,x), _filter,FILTER(_rng,(CHOOSECOLS(_rng,3)=INDEX(_rng,x,3))*(CHOOSECOLS(_rng,4)=INDEX(_rng,x,4))), _sequence,SEQUENCE(-SUM(TAKE(_filter,,-1))), _totalin,ROUNDUP(_sequence/INDEX(_filter,1,5),0), UNIQUE(TAKE(_totalin,INDEX(_rng,x,6)))))))),1)) Kind regards, OliverRe: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, you are welcome. In your latest screenshot the scenario seems to have changed completely and unfortunately i'm not sure what you want to do. Perhaps the screenshot details represent your expected result. Do the Skid 1, Skid 2 .... show what you mean by skid and does range E4:E24 show the intended output? Perhaps we only need to sequentially count how often "out" occurs within each skid. =LET(_num,SCAN(0,C4:C24,LAMBDA(a,b,IF(b="out",a+1,0))),IF(_num=0,"",_num)) Please let me know what Excel version you work with. This suggestion works with current Excel online and Microsoft 365. Kind regards, OliverRe: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, you are welcome. Please try this formula that is in cell E7 and filled down in my sample sheet. I have to enter the formula as an arrayformula with ctrl+shift+enter because i work with the download version of Excel 2013. =IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$1:D1)),1)))Re: Excel - Matching outgoing to incoming inventory based a specific conditions
=IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"closed", IF(SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"open", IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)>0,"in progress",""))) This is the formula in cell G2. =IF(A2<>"","",INDEX($A$2:A2,LARGE(IF($A$2:A2<>"",ROW($A$1:A1)),1))) This is the formula in cell B2. The formula must be entered as an arrayformula with ctrl+shift+enter if you work with legacy Excel such as Excel 2013. Both formulas must be filled down.Re: Help with formula
=INDEX('My ?ing'!E:E,MATCH(1,(MONTH('My ?ing'!B:B)=2)*(ISNUMBER(SEARCH("Discover",'My ?ing'!C:C))),0)) This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013. In the attached file i've added the formula in cell C11 in order to check if it works. =IFNA(IF(C$2="Paid",INDEX('My ?ing'!$E:$E,MATCH(1,(MONTH('My ?ing'!$B:$B)=MONTH(C$1))*(ISNUMBER(SEARCH($B3,'My ?ing'!$C:$C))),0)), INDEX('My ?ing'!$B:$B,MATCH(1,(MONTH('My ?ing'!$B:$B)=MONTH(C$1))*(ISNUMBER(SEARCH($B3,'My ?ing'!$C:$C))),0))),"") In the attached file i've added a formula that pulls dates and amount from the "My ?ing" sheet. The formula is in cell C3 and dragged across range C3:F6. In range C1:F1 are dates 01.01.2025 and 01.02.2025 that are formatted as "januar" and "februar".Re: [Excel Formula?] How to calculate a product's sales rank based on 3 criteria
=LET(arr,LAMBDA(x,CHOOSE(x,SEQUENCE(ROWS(A2:A9)),SEQUENCE(,COLUMNS(D1:F1)))), res,MAP(arr({1}),arr({2}),LAMBDA(a,b,COUNTIFS(A2:A9,INDEX(A2:A9,a,),A2:A9,RIGHT(CHOOSECOLS(D1:F1,b),4),C2:C9,">="&INDEX(C2:C9,a,)))), IF(res=0,"",res)) In Excel for the web and Excel 365 you can use this formula that spills the result. It uses HansVogelaar 's COUNTIFS and spills the result. Thanks as well to m_tarler for his PIVOTBY with very instructive use of LAMBDA and XMATCH.Re: Totalling money in categories and sub-categories across multiple columns
In the attached file the categories are text values (left aligned) and the allocated $ are numbers (right aligned). =SUM(IF($B$2:$F$9=B12,$C$2:$G$9)) This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013.Re: Formula help - not sure where to start
=SMALL(IF((A2:A12=F1)*(B2:B12<=G1)*(LARGE(IF(B2:B12<=G1,B2:B12),1)<=B2:B12),C2:C12),1) An alternative for legacy Excel could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.Re: PIVOTBY function: SUM turns into "_xleta.SUM"
You can use LAMBDA(x,SUM(x)) in english Excel instead of only SUM I experience the same problem in Excel online if i use only SUMME or MITTELWERT or ZEILEN in german Excel. The "_xleta." doesn't appear anymore if i use LAMBDA(x;SUMME(x)) or LAMBDA(x;MITTELWERT(x)) or LAMBDA(x;ZEILEN(x))..... This means that if i apply SUMME or MITTELWERT or ZEILEN in german Excel "_xleta.SUM" or "_xleta.AVERAGE" or "_xleta.ROWS" (in english) is returned from time to time. Unfortunately i don't know why this happens with SUM (SUMME) or AVERAGE (MITTELWERT) or ROWS (ZEILEN)..... In spanish Excel you can use LAMBDA(x;SUMA(x)), in french Excel LAMBDA(x;SOMME(x)), in hungarian Excel LAMBDA(x;SZUM(x)) ....Re: Help creating weighted average ranking
=SUM(MMULT(TRANSPOSE($A$2:$A$11),N($B$2:$D$11=G2)))/COUNTIFS($B$2:$D$11,G2) This works in my sample sheet. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.Re: Best method to find variances between checkbook deposits and GL deposit transactions
=IFNA(INDEX($B$2:$B$5,MATCH(SUM($F$2:F2),$C$2:$C$5,0)),"") This returns the expected result in my sample sheet. =SUM($B$2:B2) This is the formula in cell C2 that creates a helper column. The assumption is that each checkbook deposit is fully distributed to the general ledger account before the next deposit is distributed to the general ledger.Re: Updating excel database with values from another sheet
The very basic Power Query M code in the attached file returns the expected result if i correctly understand what you want to do. In the file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.Re: Combine table name with each value in the first column
=LET(sheets,A1:A5, sheetscombine,DROP(REDUCE("",sheets,LAMBDA(x,y, VSTACK(x,IFERROR(TRIMRANGE(INDIRECT("'"&y&"'!A:B")),HSTACK("",""))))),1), removeheaders,FILTER(sheetscombine,NOT(BYROW(INDEX(sheetscombine,,1),LAMBDA(x,ISNUMBER(SEARCH("Phone",x)))))), filldownmanufacturer,SCAN("",MAP(INDEX(removeheaders,,1),INDEX(removeheaders,,2), LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),"",a))),LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),b,a))), stacked,HSTACK(filldownmanufacturer,removeheaders), VSTACK( HSTACK("Manufacturer","Model","Color"), FILTER(stacked,REGEXTEST(INDEX(stacked,,3),"[A-Za-z]+")))) I understand you only want basic functions but just in case you want to log on to your Microsoft account and use all the latest functions in Excel online you can use this formula. Or perhaps other users want to try this formula which handles multiple tables in some sheets and no table in some sheets.
