User Profile
OliverScheurich
Gold Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Filter Function or any Logic
I've found your sample file in an earlier thread. However there is only one rnti in your sample file that is 384. I've attached your sample file "Filter_Function.xlsx" with suggested code. For my sample data there are now 2 queries as shown in the screenshot. One query returns the final output and the other query shows the intermediary step that includes the "spdu-" column. My sample file is "Filter Function or any Logic.xlsx".Re: power query/ dax measure , multiple conditions/ multiple answers
An alternative could be Power Query. 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.Re: unpivot from columns to rows in power query
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Naam"}, "Attribut", "Wert"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter), #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Wert"}, {"Wert.1"}), #"Inserted Modulo" = Table.AddColumn(#"Expanded {0}", "Modulo", each Number.Mod([Index], 2), type number), #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Modulo] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribut", "Index", "Index.1", "Modulo"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Wert", "employee"}, {"Wert.1", "share employee"}}) in #"Renamed Columns" The M code above returns the output in the green result table with basic transformations. The name of the blue table is "Tabelle1".Re: How do I propagate info from Master Tab to Other Tabs
Are these names of real people in your screenshot? If so please remove the screenshot. Thank you. File Attachments | Microsoft Community Hub =FILTER(Masterlist,Masterlist[Council]="Monday") I'd turn the Masterlist into a dynamic table and apply the FILTER function in the subsheets. I can't attach a sample file therefore i've added a screenshot that shows the result within the same sheet. The formula works across sheets as well. If you don't have access to the FILTER function i can suggest a solution that works in legacy Excel such as Excel 2013.Re: Nesting SortBy and Filter functions
=FILTER(SORTBY(Worklist,List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),List!I2:I500=0) Does this return the intended result in your sheet as well? Currently i can't attach a sample file that's why i've added a screenshot that shows the output if the data is in the same sheet. The shared formula works across sheets in my sample file.Re: Summing rows values while a specific column answers a specific condition.
=SUM(IF($A$2:$A$16=A2,$B$2:$G$16)) 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: Imported data pertaining to date not working with SUMIFS
=ActRevMth!D510=Revenue!B13 Does this formula return TRUE or FALSE? Perhaps there is a trailing space in one of these cells. I assume that you've applied a custom format that shows a zero value in a cell as "-". Otherwise i wouldn't understand why the formula returns "-" instead of 0.Re: Taking text from a multi-line cell and making each line its own row.
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. =DROP(REDUCE("",L2:L6,LAMBDA(u,v,VSTACK(u,TEXTSPLIT(v,,CHAR(10))))),1) With Microsoft 365 or Excel online you can apply this formula.
