Hi,
I have a table with male and female names, denoted by a second row with “m” or “f”. I want to have another table where I show only the male names. If I use the following code I get an empty row whenever the person is female:
=IF($names.B1=“m”;$names.A1;"")
Is there a way to not have those empty lines and instead display the next male name right away?
Thanks!
Welcome!
Yes, it’s not difficult. The operation of selecting values by condition is called “filtering”. Therefore, go to the Data - More Filters - Standard Filter menu, indicate that you need records with m and indicate that the filtering result should be copied to another location.
Thanks for helping! The document will be used by lots of novices who don’t know how/where to click. That’s why I’m looking for a self-updating solution like an IF statement. So while filtering would do the job I’m afraid that would create more problems than telling them just to copy all male names in the first place. However if there was a self-updating way …
But it’s not difficult - create a small toolbar for them in this spreadsheet with a single “Refrashe Range” button and tell them three times (in the worst case, seven times) when this button should be clicked.
Well, if you insist on a formula, then yes - it can be done with a formula. But get ready for the fact that this will be somewhat more complicated than the usual IF() function
Create a helper column with a formula like
=IF($Source.B2="m";ROW();"") - this does the same thing as the IF() function in your question, but it does not immediately return the name, but only the number of the row containing the man’s name.
Now use these numbers in a formula like
=IF(ROW()-1>COUNT($F$1:$F$300);"";INDEX(Source.$A$1:$A$300;SMALL($F$1:$F$300;ROW()-1)))
You don’t want to use an auxiliary column? You can do without it. But then the formula will become even more complicated:
{=IF(ROW()-1>COUNT(IF(Source.$B$1:$B$300="m";ROW($B$1:$B$300);""));"";INDEX(Source.$A$1:$A$300;SMALL(IF(Source.$B$1:$B$300="m";ROW($B$1:$B$300);"");ROW()-1)))}
All three methods are shown in This spreadsheet.ods (16.4 KB)
Thank you very much, John! This was so helpful, thanks for your time and all of your suggested solutions!