•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Assistance with advanced filter options
Our take
If you're grappling with filtering data in an Excel report generated from less-than-ideal software, you’re not alone. The challenge arises when filtering for specific names, like Bob, inadvertently hides crucial comments associated with those entries. Instead of relying solely on the standard filter function, consider alternative approaches, such as using advanced filtering techniques or creating a pivot table. These strategies can help you view Bob's name alongside his comments seamlessly, transforming your data into a more user-friendly format.
I 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:
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:
And not like this:
Perhaps the filter function isn't the right approach, so I am open to alternatives.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Help for a vocab sheet - how to filter ?Hey guys ! Seeking help for a German vocabulary sheet I'm doing to keep track of new words I'm learning. I have a 'master' sheet with everything - but I'd like to create sub categories sheet based on the grammatical function of the words (verbs, gender of the nouns etc...). How do I filter that and keep it updated so that every time I write a new word it automatically comes up in the other pages ? I have multiple columns in the 'master' sheet (see picture). Hopefully what I want to do is clear and you guys can help me, I'm really bad at excel and just trying to make my vocab learning a bit easier ! thanks x https://preview.redd.it/phz11z3369tg1.png?width=2820&format=png&auto=webp&s=74ec8f4f69653c154c908080105a677bbd041388 submitted by /u/mariectu [link] [comments]
- filter out column by color (or otherwise)?I'm trying to create a simple table that will have some columns that are "calc" columns, by which I mean that they will be used to calculate values for other columns, but I don't want them to be visible on the final output. I can hide each one before printing, but since I'm setting this up for other users to input data, and they aren't super comfortable with Excel, it would be best for it to be something they can do in just a few clicks with basic instructions. I was hoping I could just use a text or cell color, and filter them out at the end, but there doesn't seem to be a way to filter columns, only rows with this method. I'm also not sure if that would break the formulas. Does anyone know of a way to accomplish this? The 'calc' columns are spread throughout the document, so I can't use the print area feature. EDIT: Excel 2019 (v2508) submitted by /u/InspectDurr_Gadgett [link] [comments]
- How to confront multiple sheets against each other?English is not my first language, if anything doesn't make sense pls ask. I am currently trying to filter a bigger excel for a conference programm, their file was divided as such sheets: Day 1, Day 2, Day 3, Day 4 -> this containes, for each day in the title of the sheet each conference title for a given room at a given time. I created other sheets, which i named as the main topic. for example: Hybrid if the sheets containes mentions of hybrid as a word in the session or title of the conference. I am now stuck, since I only searched my main interest and created new sheets only for the word I choose. I now have a incomplete subdivision, since I didn't copy each and every conference. I would like to create a new sheets named OTHERS in which I import any conference I don't have in the specific sheets I created. So for example if in Day 1, row7 column 15 never appear in each new sheets I created (hybrid, liquid, ...) I want that row imported in the sheets OTHERS. Is there a way to do this, even only by finding the single row# column# and then I can manually create the table for the new sheets OTHERS. Or I just need to filter manually or use an AI (which i don't like to do since I want to use excel if possible) submitted by /u/Candid-Note6320 [link] [comments]
Tagged with
#rows.com#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#Excel alternatives#Excel compatibility#digital transformation in spreadsheet software#financial modeling with spreadsheets