•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sync or map data of two automated columns to the filtering systems of other columns
Our take
In this scenario, you aim to sync columns C (Assigned Codes) and D (Positions) with the filtering systems of columns A (Country) and B (Leader Assigned). Using dynamic array filters allows real-time updates from the source workbook, but you face a challenge: the codes and positions do not align correctly when filtering. As a result, selecting a country like the USA only reveals partial data. We'll explore solutions to effectively map these columns to ensure accurate filtering and complete visibility of your data.
Context: 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.
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! :))
[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]
- Best way to automate result filtering?Help- I'm just starting to learn about automation within Excel and I'm not sure what I even really need to be asking. The short version is, I need to be able to dynamically sort out blank columns based on filter results for another column. More specifically, one of my job dutirs is pulling and distributing customer satisfaction survey results across our system. Someone along the line decided the best way to do this was with a single, massive survey on Survey Monkey, with location based filters automatically giving the customers site specific questions. What this means for me is that every week I'm using views within the survey monkey analysis screen to pull results individually for each location, then unzipping and naming them, before finally being able to send them out to the various teams. If I pull the entire survey with no views applied, there are a ton of columns, with each location having a certain block of columns containing results. E.g. location a results are in columns d-f, location b results are in columns g-i... Etc. What I would like to do is pull the entire block of results and then use some sort of automation to sort results automatically, taking this from an hours long process down to a matter of minutes. Should I be looking at power query? Macros? Is this functionality built in to an existing tool, like pivot tables or something? Any direction on where I should be going to learn more is appreciated. submitted by /u/Ok_Perception3325 [link] [comments]
- Transfer Data from One Workbook to Another (SPILL Issue)Hey! I’m working on an Excel setup where the workbook on the left is my source and the one on the right is my destination. My goal is to automatically transfer the “Given IDs” from the source to the destination based on their assigned codes. I fixed the one‑to‑many lookup issue, but now I have another problem — when I type an existing code again in the destination (highlighted in green), it shows #REF! instead of its IDs. Is there any way to fix this without losing the automatic transfer I already have? + A user suggested to the COUNTIF into a MOD. I'm not sure how to operate this so I hope anyone could teach me. + "FILTER('[book 2.xlsx]Sheet2'!B:B,ISNUMBER(XMATCH('[book 2.xlsx]Sheet2'!A:A,B:B)))" = this also helps but I also encounter the aforementioned problem (in my real workbooks, there will be times when I need to enter the same codes directly in the destination, and I don’t want to keep going back to the source workbook to re‑enter them all over again.) Thank you so much in advance! https://preview.redd.it/cb9jv0h57ryg1.png?width=1314&format=png&auto=webp&s=b26b1b2886d53f8fe94b76735e4de21ea9a3b866 submitted by /u/jeankrstein [link] [comments]
- Issue with Excel Power QueryIn my excel workbook I have a long string of queries to get the results I want, however I am noticing a small number of duplicates that SHOULDNT be able to exist. In my first query in this string, I am adding a new column (SelectionBucket), based on two other columns - Works. Then taking this SelectionBucket column, and adding another column (IsRequiredBucket) based on [SelectionBucket] returning one of the required values - Works. I then am adding an index at this time (CourseIndex) - Works. Result: Courses have Index, and SelectionBucket and IsRequiredBucket as options. Q2 (Reference to Q1): Adding Column (IsRequiredCandidate) where [IsRequiredBucket] = True - Works. Filters out to ONLY true values next, and sorted on (Name) (Ascending), (SelectionBucket) (Ascending), (EMark) (Decending) - Works. Result: Filtering down to only RequiredBuckets, sorted by Best to Worst. Q3 (Reference Q2): I group the rows based on (Name) and (SelectionBucket), call it [AllRows]. Add Column (TopRequired) with Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),1) to return the BEST value - Works. Expand the [TopRequired] Table, excluding Name and SelectionBucket - Works. Add column (SelectionType) = "Required" This is where I am not sure if it is working or not, because for 99% of my data, this works. But for some of the entries, this isn't working. Add one more column (IsRequiredSelected) to check (SelectionType), if "Required" = TRUE. Result should be: Selection of one result for each of the buckets available per entry, and setting its (IsRequiredSelected) value to TRUE. Q4 (Reference Q1): I merge Q4(which is just Q1), to Q3, matching on (CourseIndex), Expand the merge (SelectionType). Rename (SelectionType) to (RequiredTag). Add column (IsRequiredSelected) checking [RequiredTag] to return TRUE for "Required", FALSE otherwise. Q5 (Reference Q4): Add Column (IsAdditionalCandidate) checking [IsRequiredSelected] = FALSE. Filter (IsAdditionalCandidate) for only TRUE values. Sort by (Name) (Ascending), (EMark) (Descending). Result: Check if (IsRequiredSelected) = TRUE and clearing them out. Q6 (Reference Q5): Group by Name -> [AllRows] with operation of All Rows. Add column (TopAdditional) coded =Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),7). Expand the table [TopAdditional] excluding (Name). Add column (SelectionType) = "Additional" Result: Taking only records that are marked as "Additional" and taking the best 7 results for each (Name). Q7 is an appended query of combining Q3 and Q6, where it should take the Q3 Results, and adds the Q6 results to it, which should result in NO duplicates. Issue: I am receiving some of my entries as a duplicate through a query check, where I see the one record as both an Additional and as a Required. I am not sure WHY or where it is broken, other than where I think it is... I realize I could have done this in less Queries, however I wanted to verify along each step of the way if something went wrong, so that I could fix it as a portion, instead of having to delete and re-write everything. Please note that I CANNOT share the excel file data itself, as it contains confidential information within it. If I haven't explained a step clearly enough, please let me know and I will try to add further information on it. submitted by /u/DLCamilla [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#financial modeling with spreadsheets#real-time data collaboration#rows.com#big data management in spreadsheets#conversational data analysis#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#AI formula generation techniques#real-time collaboration#formula generator#Excel alternatives#automated anomaly detection#Excel compatibility