Horizontal automatic filters

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

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

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.

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

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

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.

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?

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.

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

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 

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 ...

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

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

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

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

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.

Thank you for all your information.

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

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