•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Filter Function to Filter large data set to Multiple Users
Our take
If you're looking to streamline data management and enhance your productivity, the “FILTER” function can be a powerful tool for narrowing down large datasets. In your scenario, where you need to extract entire rows based on multiple user IDs from a dynamic dataset, crafting the right formula is crucial.
I am trying to use the “FILTER” function to filter a large dataset that changes each day down to a specific group of users. I am getting stuck when trying to add a second “User” to pull from the data.
My Setup:
one sheet named “Dataset” is the data. It is many columns and I need the whole row for that user to be brought into the results. The column that houses the User ID is column R and has a couple hundred different users.
Covers cells A1:AK11932
Second sheet named “users” is the list of user IDs
Covers cells A1:A12
Third sheet named “results” is where I want the rows of filtered data to pull into.
I have gotten a lot of different errors and can’t seem to nail down how to write the formula so it pulls every row with a user ID from my user list. The formula helper excel offers is not really helping.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- 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]
- 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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#large dataset processing#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#AI formula generation techniques#Excel compatibility#row zero#formula generator#Excel alternatives#natural language processing for spreadsheets#financial modeling with spreadsheets