User Profile
OliverScheurich
Gold Contributor
Joined Oct 04, 2021
User Widgets
Recent Discussions
Re: Replacing a string of words with a single number
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. 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. { { "Kunming", "14" }, { "Hongkong", "66" }, { "Suzhou", "8" }, { "Wuhan", "91" }, { "Dongguan", "4" } ,{ "Zhengzhou", "101" }, { "Harbin", "3" }, { "Hangzhou", "88" }, { "Qingdao", "79" }, { "Foshan", "140" }, { "Shenzhen Tianjin", "42" }} In the example in the screenshot we replace "Kunming" by "14" and "Shenzhen Tianjin" by "42" and so on. In the second sheet in the attached file is a formula that replaces strings with numbers.Re: Formula help
=LET(colsfromsheets, CHOOSECOLS(VSTACK(Tabelle1:Tabelle3!A3:J200),2,5,7,10), IFNA(VSTACK({"Non-Catalogue".""."Catalogue"."", "Product"."Number Required"."Product"."Number Required"}, HSTACK( FILTER(CHOOSECOLS(colsfromsheets,1,2),CHOOSECOLS(colsfromsheets,2)>0), FILTER(CHOOSECOLS(colsfromsheets,3,4),CHOOSECOLS(colsfromsheets,4)>0))),"")) Lorenzo has provided a solution for Microsoft 365 in the meantime. I'd have tried to achive the result with the formula above.Re: Formula help
=IFERROR(INDEX(Tabelle1!$B$3:$B$1000, SMALL(IF(ISNUMBER(Tabelle1!$E$3:$E$1000), ROW(Tabelle1!$E$3:$E$1000)-2),ROW(A1))),"") This formula works in my sample file in modern and legacy Excel for the non-catalogue products of sheet "Tabelle1". 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 2024. It returns the results from one sheet to the summary sheet. You can adapt these formula to all four sheets. The other formulas are in the attached file. If you have access to modern Excel and it's functions such as VSTACK, CHOOSECOLS, FILTER... then there's a possibility to return all results from the four sheets in one step.Re: Formula to retrieve data from several sheets and return values from given criteria.
=IF(ISERROR(VLOOKUP($A10,EY!C$3:C$33,1,FALSE)), IF(ISERROR(VLOOKUP($A10,MY!C$3:C$33,1,FALSE)), IF(ISERROR(VLOOKUP($A10,LY!C$3:C$33,1,FALSE)),"Not Found","LY"),"MY"),"EY") It doesn't return the NAME error if i use " instead of ” in my sample sheet. However i'm not sure what you exactly want to do.Re: AVERAGE reduced lambda on GROUPBY or BYROW formulas not working on specific workbook.
Unfortunately i don't know why this happens. However you can do many replacements in one step. With the sortcut Ctrl+F you can open the search and replace window (suchen und ersetzen in german Excel) and then replace for example ;SUMME by ;LAMBDA(x;SUMME(x)) In your scenario it should be ,PROMEDIOA by ,LAMBDA(x,PROMEDIOA(x)) In my sample file in german Excel i've made 23 changes in one step. Using ";SUMME" with ";" in the beginning should prevent unintended changes in other parts.Re: Display Last Value in Column when certain criteria match
=INDEX($D$4:$K$50,LARGE(IF(INDEX($D$4:$K$50,, MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0))<>"",ROW($D$4:$D$50)-3),1), MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0)) This formula works in my sample file and in modern and legacy Excel. 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 2024. The formula is in cell R2 and filled down.Re: How to create a multi-tiered percentage-based bar chart?
I've applied Power Query to convert the blue table into the green result table. In the attached file you can add data to the blue dynamic table. 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. The stacked bar chart is updated dynamically.Re: Index & Match Formula Not Working
=INDEX('Sheet 1'!C:C,MATCH(1,('Sheet 2'!A1='Sheet 1'!B:B)*('Sheet 2'!A2='Sheet 1'!A:A),0)) This formula works in my sample file and in modern and legacy Excel. 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 2024.Re: Different functions for each column in a subtotal
With modern Excel and with legacy Excel such as Excel 2013 you can use Power Query. In the attached file you can add data to the blue dynamic table. 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. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.Re: Calculate hours using pivot table
An alternative could be Power Query that works in Excel 2013 and more recent versions of Excel. In the attached 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. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet and the blue tables horizontally.Re: Need assistance to correct a formula
=IF(B2="Sun",IFERROR(SUM(INDEX($C$2:C2,LARGE(IF($B$2:B2="Mon",ROW($B$2:B2)-1),1)): INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1))), SUM(INDEX($C$2:C2,1):INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1)))),"") This formula works in my sample file in Excel 2013 and in modern Excel. 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 2024. The formula is in cell I2 and filled down.Re: Index and match mystery (for me that is...)
=INDEX(C21:C36,MATCH(1,(A21:A36<=D48)*(B21:B36>=D48),0)) This formula works in my sample file and in modern and legacy Excel such as Excel 2013. 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 2024.Re: Logical test for same text string existing anywhere in both ranges.
=IF(SUMPRODUCT(COUNTIFS(Tabelle1[@[Wri1]:[Wri4]],Tabelle1[@[Dir1]:[Dir3]]))-MMULT(MMULT(N(Tabelle1[@[Dir1]:[Dir3]]="(n/a)"),ROW($1:$3)^0),MMULT(N(Tabelle1[@[Wri1]:[Wri4]]="(n/a)"),ROW($1:$4)^0)),"Y","") This formula returns the expected result according to what i understand.
