Horizontal automatic filters Solved

Question

Hello, We all know how automatic filters work in Excel. But would you know how to manage the same principle but applied to rows rather than columns? Thanks in advance.

eriiic · Answer

Hello,  To my knowledge, no. But you have the special copy/paste option by checking 'Transpose' which turns a horizontal matrix into a vertical matrix.  Eric

Vaucluse · Answer

It's true, eriiic, but watch out for the damage if your cells contain unblocked formulas...! At least, I don't know how to transpose them without them being locked (except by cutting instead of copying the original, but apparently, the transpose option is no longer available in that case) If you have the tip, I'm interested CRDLMNT -- Science without conscience is but the ruin of the Soul

eriiic · Answer

Hello Vaucluse,  Yes, it's good that you brought this up. Indeed, if there are formulas within the copied area (to transpose) that reference cells outside of that area, Excel gets confused.  In this case, here are 3 solutions:  - check 'values' in addition to 'transpose' in the paste dialog. Quick but valid only if it's for a temporary filter and working on the values is sufficient. - Change those references to absolute - Name the cells outside the area and replace the relevant references with their names (or have developed good habits and named those cells from the start, but that's for the brave who anticipate well ;-) )  Best regards, eric

odweb · Answer

Hello and thank you for your responses. Unfortunately, I cannot move my table because I need to be able to use the automatic filters vertically as well.  Here is how the table roughly looks:   a b c 1 fm aw fm 2 yes no yes 3 yes yes no 4 no no yes 5 yes no no etc......  In fact, I want to be able to visualize all the yeses for fm, so I need to filter yes vertically and fm horizontally. If there’s no solution, I still have the tedious option of hiding the non-fm columns, but when I make them reappear, you can imagine the time it takes to redefine the hidden columns and those that are not...  Best regards.

Vaucluse · Answer

Hello Odweb If it's just about visualization, you can always create a copy sheet for each column header, linking it to the input sheet using conditional formulas: On each of these sheets, you can display only the "yes" from the "fm" columns, for example, by including in each corresponding cell of your table a formula like, referring to your example which we assume is on sheet1: On sheetfm, repeat the column headers from the initial table in each cell enter the formula, starting from the first YES or NO: =IF(Sheet1!B2="NO"; ""; IF(B$1="fm"; Sheet1!B2; "")) The formula first eliminates all "NO" and then only considers the column headers. You just need to change the name "fm" for each of the sheets you want to create Thus, by selecting "Sheetfm", you will only see the cells containing a yes in the fm columns You can also present your additional sheets differently by marking only the condition "YES" on columns referring only to the corresponding columns of sheet1, it's slightly more tedious (cell offset) in terms of setting up the formulas. Finally, you can also make it easier to read your results on the "Sheetfm" and others by applying conditional formatting to your cells For example, by graying out all empty cells: Format / Conditional Formatting / The formula is / =B2="" / choose your "hide" format, this applies to all cells By selecting "Sheetfm", you will have a permanent view of the yes in the fm columns and only them. This is just a trick to facilitate visualization, which will not be far from what you would obtain on a sorted sheet. (There is also the possibility of creating a copy sheet, a duplicate of the first, which would cross the cells and bring you a 90° copy of the first table, thus allowing you to sort the columns on one sheet and the equivalent of the rows on the other) That said, there are other ways through macros; I would have solutions on the subject but they would only be tricks compared to what VBA can achieve, and I leave it to others more competent to advise you if they have them. Best regards

eriiic · Answer

Hello,  Just a small thing that I can't grasp, so I modified your example and put A2 to "no":  a b c 1 fm aw fm 2 no no yes 3 yes yes no 4 no no yes 5 yes no no etc......  If your goal is to display all the "yes" fm and you start by filtering the "yes" from column A, you are hiding all the "no" lines, including the "yes" fm from C2, for instance. Is that what you want, and what is your need exactly?  I think we should lean towards the idea of Vaucluse and not use filters.  Eric

odweb · Answer

Thank you for your responses, but I believe I need to clarify my expectations correctly. Moreover, it seems delicate to revisit the very structure of the table, which is already extremely large and complex. In fact, I am conducting an audit. pra = practitioner int = intervention pans = staff f = false v = true a = other  I need to be able to search: - by practitioner then by true, false - by staff by true, false  And to avoid the inconvenience that eriiic pointed out correctly and that I had not thought of, I need to first filter horizontally before doing so vertically.   1 a b c d e f g h i 2 Pra Bou Bou Bou Bou Bou Bre Bre Bre 3 Int colo colo c/fib colo colo c/fib c/fib colo 4 Pans sd md pb sl fm sm md fm 5 item0 f f f v v f f f 6 item1 v v v v v v v f 7 item2 v v v v v v v v 8 item3 v v v v v v v v 9 item4 v v v v v v v v   Thank you in advance for your help and support.  Best regards.

eriiic · Answer

I'm sorry, but I don't have any context to translate your message. Could you please provide the text you need to be translated?

odweb · Answer

This table should allow me to highlight certain individuals and all the poorly filled files based on a specific item. Therefore, I could quickly say how many times fm will have had f on item 3, for example, for a specific intervention title. Best regards.

eriiic · Answer

So it's the total of v of f by Pra and by Pans that interests you... The best would be, I think, to develop a specific function, but since your examples change, it's difficult. Should we count (if you confirm the counting) yes/no, v/f/other, or something else? And be able to count by Pra then by Pans (there I think it's yes)? But in your last message you added "for such intervention title"...   sd sd md  colo c/fib colo  v f v f v f item0 8 2 item1 15 5 item2 12 4 item3 22 10 item4 7 6  Is it something like that that you want? Clearly define your needs and give the most fixed rules possible based on your original data. eric

eriiic · Answer

a little problem, it's better this way :-)   sd sd md colo c/fib colo v f v f v f item0 8 2 item1 15 5 item2 12 4 item3 22 10 item4 7 6

xkristi · Answer

Hello!   It would be clearer for us if you could upload a part of your file (keeping the size in mind)  and what kind of result you would like  on  https://www.cjoint.com/  and give us the link.   -- xkristi  Life is more ...

Raymond PENTIER · Answer

Hello, odweb.  I spent my morning (local time) thinking about your problem. Attached are 2 Excel files, one containing the 2 demonstration tabs, the other allowing you to print screenshots to visualize what it looks like while you examine the formulas in the first one. If you think my method could be helpful, I’m willing to describe in detail how I went about it and why I chose this or that option (this offer also applies to Vaucluse, argixu, eriiic, m@rina, and the others...)  https://www.cjoint.com/?jCtWidTvG6 https://www.cjoint.com/?jCt4lJgEKI  -- A retiree basking in the sun of the Caribbean :-) Raymond

eriiic · Answer

I don't know if it will suit him, but it's a nice way to approach the issue pretty eric

Raymond PENTIER · Answer

Hi Eric,  Thank you for your compliments, and I appreciate you taking a look at my files; I'm here if you need any clarifications, and thanks in advance if you have any suggestions to simplify or improve my formulas. -- A retiree in the sunshine of the Caribbean :-) Raymond

eriiic · Answer

Hello Raymond,   I have no comments on your formulas; it’s a clever way to reach the result and to remember it.   Just one thing that you haven't used but you probably know, I'm still sharing it with you.  The pra, int, and pans lists are apparently entered manually (still due to this hz instead of vt presentation issue). In this case, it’s interesting to use 'tools / list / create a list...'. It allows you to have a custom filter in the header (which won’t be useful here because how to retrieve the filtered value? If you know, I'm interested) that gives access to sorting the list; it can be useful.   Another thing that I only realized this morning: it seems he wanted the results by item... which complicates things a bit.   I think the macro is necessary, but since he seems to be busy elsewhere...  Eric

odweb · Answer

Hello, here are the screenshots: http://odweb.free.fr/audit0.JPG http://odweb.free.fr/audit1.JPG I want to filter, for example, all the interventions of md and all the v I also need to be able to filter the interventions of md with this or that Dr only and this or that intervention horizontally... and vertically be able to filter all the v, for example. There, I don't think I can be more thorough. Sorry for not being able to respond sooner, and thank you again for your attention to this matter. Best regards.

odweb · Answer

Thank you for all your information.

eriiic · Answer

Good evening odweb,  I hope you haven't lost too much hope and that you'll still take a look here I propose a solution: http://www.cijoint.fr/cij28924553327825.xls  The operation is as follows: double-clicking on a value in rows 2 to 4 filters horizontally based on that value. You can combine filters and refine further. (e.g.: on Dr1 then colo shows you all the colos of Dr1 and the count of v/f. A reset button allows you to unhide all the columns. I haven't filtered vertically as you requested (but I did include Excel's auto filters) because for me filtering on a v would hide other v's or f's. In exchange, I've inserted 2 columns with the total of v and f displayed (according to the filter), and also, double-clicking on a v or an f will filter that value horizontally (for example, if you see a high number of f's on an item, you can filter them. We could consider a counter for the panseurs, Dr, and interventions displayed to see who is most present in this case... Constraints: use this workbook (macro upon opening), and use Sheet 1 for your data (macro using the events of this sheet only). Cell A1 of this sheet is off-limits: I store a variable there. Let me know what you think  eric

odweb · Answer

Hello Eriic,And thank you very much!Your work is really amazing!!!Now I only have one problem: including it in my existing table. Indeed, you have set constraints for the formatting, which is completely understandable.However, I find myself in a situation where my audit is already completed, and I am already working on 3 sheets.Sheet 1 = result visualization sheet with 3 identical tables, t1 = general summary, t2 = results from sheet 2, t3 = results from sheet 3)Sheets 2 and 3 = identical table, I had to use 2 pages to insert my 300 studied files, and Excel limits me to 250 columns per sheet, which means 250 files. For this case, I've studied 300, hence the necessity to increase the number of sheets. My table is therefore ready for my next audit for 500 files.I am trying to put this into practice by inserting my info into your table as soon as possible, but I am not sure I will have free time soon to do it.In any case, I will keep you updated as soon as possible.Thanks again for everything.Best regards.odweb

eriiic · Answer

Hello,  When you say your table is ready (for 500), does that mean a blank table? If so, please drop it here (the xls, not jpgs!!) And what you requested corresponds to the analysis of t1 or t2 and t3?  To copy the macros into your workbook (but if the rows/columns are different, you will need to adjust): - Right-click on the tab name of my sheet 1, choose 'View Code', copy all lines - Right-click on the tab name of the sheet you want to analyze, paste  - If your sheet cannot receive totals in columns B:C, comment out (with a ' in front of the line) at the bottom  in front: Cells(ligne, 2).Value = totalv and the following line We will skip the opening macro; the only thing to compensate for will be to never start with RAZ after opening (otherwise, minor error) but only after a filter  To make the filter work on different lines, adjust  Case 2 To 10 'horizontal filter lines  to what you want.  The counter part of v/f will only work on rows 5 to 10 currently....  We need to know exactly the structure of your sheet and what you want to do in which area if you want something tailored to your problem... Eric

Horizontal automatic filters

21 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.

Wake on lan with livebox

"freebox" is blinking (tv)

How to access the bios

[freebox] what customer service after lightning strike?