•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Using CHOOSECOLS + FILTER to return only specific columns
Our take
Unlock the potential of your data with a simple yet powerful dynamic array trick using CHOOSECOLS and FILTER. Imagine you have a table, TableData, featuring columns such as ID, Name, Department, Location, and Salary. By filtering on Department and Location, you can easily return just the Name and Salary columns with the formula: =CHOOSECOLS(FILTER(TableData, (TableData[Department]=G1) * (TableData[Location]=G2)), 2, 5). This approach not only streamlines your data analysis but also enhances accessibility. Are you exploring
Hi all,Sharing a small dynamic-array trick using CHOOSECOLS + FILTER.Say you have a table TableData with columns: ID, Name, Department, Location, Salary.You can filter by Department and Location, and return only Name and Salary like this: =CHOOSECOLS( FILTER( TableData, (TableData[Department]=G1) * (TableData[Location]=G2) ), 2, 5 ) G1 = Department, G2 = Location, and 2, 5 are the column numbers (Name, Salary) in the filtered spill.Anyone else using CHOOSECOLS in interesting ways?Do you want me to also add a tiny data example in the post so it’s clearer for people replying?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Assistance with advanced filter optionsI have a report that is being generated through some janky software, and unfortunately the way I am getting the excel document can't be changed. This is a very basic example of what the sheet looks like, the actual generated report is thousands of rows: https://imgur.com/a/d0jNtNx For example, I want to filter for Bob's name in column A and also see all of Bob's associated comments in column B. If I apply a filter for Bob in column A, it hides everything in column B that are the relevant comments (obviously). To clarify, I want a filter to make it look like this: https://imgur.com/a/pgVJsLm And not like this: https://imgur.com/a/wCev8uH Perhaps the filter function isn't the right approach, so I am open to alternatives. submitted by /u/drspudbear [link] [comments]
- Filter but with Array Condition?Hello, I have two sets of data that changes from week to week, with IDs & Dates. I'm trying to create a dynamic table that counts how many unique calendar days are associated with each ID. Date table has Ids & Dates (ID in col 1, Date in col 2) - it's an array starting in A4 ID table has IDs for the week to be needed (Just an array of IDs that's on T4) I came up with Count(unique(filter(choosecols(A4#,2),choosecols(A4#,1)=T4#))), but that doesn't work because T4# can't be an array. It works with T4, but how can I dynamically put it on every row? submitted by /u/Bobatwork99 [link] [comments]
- Excel Performance optimisation tips!Working in demand planning I have got it the point where I am making some pretty advanced files using a suite of techniques. My files often have lots of rows, with lots of Columns of complex formula including with sumifs, xloopup, ifs & Let. I’ve not advanced to using tables regularly though as I find the constraints & syntax annoying but am trying to get there & have started using power query to blend data for output analysis. The problem I am encountering is I filter ALOT drilling down into product groups etc, & excel tends to ‘hang’ a lot with ‘Not Responding’. Now I’m not sure it’s due to an underpowered machine (intel core i7 HP Elitebook) or, more likely lots of complex formula referencing ranges or tables. My question to the hive brain: share your optimisation tips & tricks! -Can Lamda combined with Let speed things up? -Are Tables vital to speeding up complex sumifs & lookups? - are match helper columns combined with Index leaner & faster than xlookup? Hit me with best tips & tricks! submitted by /u/NZGRAVELDAD [link] [comments]
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]