User Profile
ColinJHarrison
Brass Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Getting a table to dynamically match a variable range's row count
Hello Excel wizards, I find myself doing something which feels really clunky, and I'm hoping there's a smarter solution. I import a filtered range of values using a FILTER() call (let's say in A2) which will populate columns A2:Bx. The row depth (x) is variable, depending on Power Query updates of the source data that FILTER() is acting on. Next to this data, in C2:Dx (let's say) I want to construct a table that adds new calculations based on the values in the FILTER() range. I'll attach an example workbook. (Why a table? Because it's convenient for formula editing, and this table itself might be referred to in other functions elsewhere...) How can I get the table in C2:Dxto dynamicallymatch the row count (x) of the filtered range in columns A2:Bx? I don't know any way of doing that. You can't use a FILTER() function inside a table (right?)*, which is why I'm just placing my table (C2:Dx) next to the FILTER() output. My "solution" (shown in the attached workbook) is to build a table with about twice as many rows as I anticipate ever needing, and to use some kind of masking formula in the table cells that starts with IF(A2="","", ...) to make sure that the table rows below the FILTER() range don't fill up with #N/A! errors. But this feels like a really dumb solution, and of course if I ever guess wrong, and my FILTER() range is longer than my table, then I get a bunch of values that don't calculate at all. I can tolerate this in something I build for myself, but it's an awful thing to leave in a solution built for a client! I would love a better way of getting my table to dynamically match the row depth of my FILTER() ouput! Or indeed just a better solution to the whole problem! Any ideas? Thanks *Note, in my attached simple example, I am only using FILTER() to zap a column, which I know I could do differently. In the real world this is liable to be an embedded FILTER(FILTER()) call acting on both columns and rows of the source data, so I can't easily see how to make that into a column formula in the table itself, for example...Solved4.9KViews0likes6CommentsPassing a Table as a variable (in a LAMBDA() function)
Hi, Why can't I do this (in Excel 356)? I have a table called Table I'm trying to build a LAMBDA() function that will contain something like the following (this is the test syntax...): =LAMBDA(T,T[#Headers])(Table) But the T[#Headers] statement is obviously just flat out illegal, because the editor won't even accept it! ("There is a problem with this formula..."). Why is that? How can I construct a reference to a Table that will allow me to pass the Table as a variable and then grab a range from it? Thanks.Solved5.2KViews1like6CommentsFiltering poorly formatted source data...
Hi, I have a weirdly arranged data set that I am importing into Excel via Power Query. My source has the structure I have indicated in the attached example (although the example is greatly simplified), i.e. the columns are divided by sub-headers (highlighted in the example), and the values that I am interested in – the Regional data – repeat multiple times in each subsection. What I need are Subsection:Region:Value associations, i.e. 1A:Region1 : Value1 1A:Region2 : Value2 1A: Region3 : Value3 1B:Region1 : Value4 1B:Region2 : Value5 etc. (They don't have to be represented like that neccessarily, but that's the data structure I need). Note that the subsections (1A, 1B, 1C) are not necessarily the same length: some subsections might be missing data on one or more sites, meaning that the row count for each section is unpredictable… (so, I can’t just use Group by number of rows, in the Power Query import, for instance). This could also change between imports, so any solution has to be able to adapt to variable row counts. The way I initially did it was to remove the 2 Site columns altogether in the Power Query import, leaving just Region and RegData, and then just call UNIQUE() on the resulting import table, but that only works if no region has the same value in 2 different subsections, which could happen, and would break downstream calculations if it did (because my UNIQUE() function would return a range missing a row). It would also be a problem if, by chance, one subsection had no values for a particular Region. I need the solution to return a null value in that case (e.g. 1B: Region2 : NULL), or in other words, the solution has to return a 3x3 range, even if some values are empty, and/or some values are the same... I hope that makes sense. Many thanks for any suggestions.Solved2.2KViews0likes6CommentsManually updating Tables?
Hi, I have created a tool to be used by people who are not excel pros. The tool accepts data from 2 or more csv-style reports (output from other programs) and uses it to calculate a data presentation that corresponds to my users' needs. What I want is a painless way for my users get that csv data into the "back end" of the tool I have made for them. Because of how I have set up this tool, the tabs that accept the raw data are formatted as Tables. Problem is, for now, copying the data into these Tables is messy. Each csv report will always have the same column order and header names, but the number of rowsin each report will be different every time. Just copying the whole csv report and overwriting the whole table doesn't work, as it deletes the Table, which will break the calculations in the main tab. But any other copy-paste routine is also fraught, (as in the case where the incoming data has less rows, and the result of the copy-paste exercise leaves a few rows of old data at the bottom of the Table...). Also, at least one of these csv reports is liable to have anything up to 5,000 rows. I don't want to require my users to manually select that many rows for copying and pasting if I can avoid it. I'm sure there's a smarter way of doing this, that is easy for my non-technical users. For now, I do not want to get into Power Queries etc. and I can't use VBA (not permitted on my client's system). Users need to be able to update the tool whenever they want to, and need to be able to understand what they are doing. I would appreciate any suggestions. Thanks.Solved1.3KViews0likes4CommentsGetting multiple values against unique names...
Hi, I have a data set with names in one column (A) and values in another (B). Each name:value pair is unique, but both A and B have duplicates. I need to extract a list of unique names (that's easy enough UNIQUE(A:A)), but then next to each name, somehow list (horizontally - i.e. catenated into a single string in one cell, or else spread horizontally across columns) all the values that occur against that name. Any suggestions as to how I might do this? ThanksSolved4.3KViews0likes8Comments
Groups
Recent Blog Articles
No content to show