Filter 2 columns at the same time in a table

[rlclynn]

Hi. Newbie here! I have been trying to figure out how to filter two columns at the same time. In this instance, I want to filter the "SPLIT SALES" and "SPLIT SALES 2" columns by "HIPPKE". Essentially pulling all "HIPPKE" sales. Is this possible? TIA!

[#2]

@rlclynn 

Insert a helper column:

=OR(Sales_Split="HIPPKE",Sales_Split_2="HIPPKE")

And filter for TRUE.

[#3]

@rlclynn 

You might add a column with formula =OR([@[SALES SPLIT]]="HIPPKE", [@[SALES SPLIT 2]]="HIPPKE") and filter that column on TRUE.

Alternatively, use Advanced Filter 

[#4]

@HansVogelaar 

Great idea, Hans. 

[#5]

I have to filter multiple persons at end of month. Is there an easier way to accomplish this?

[#6]

@rlclynn 

Unpivot both columns.

[#7]

Not sure how to do that. I do not have this in a pivot table.

[#8]

@rlclynn 

You could do it with Power Query.

[#9]

@rlclynn As a variant, the COUNTIF function can be used in a helper column to filter by either a single SalesRep or multiple SalesReps. For example:

=COUNTIF(tblData[@[SALES SPLIT]:[SALES SPLIT 2]], $K$1)>0

...where cell K1 contains a data validation picklist to select the desired SalesRep.

=SUMPRODUCT(COUNTIF(tblData[@[SALES SPLIT]:[SALES SPLIT 2]], tblCriteria[SalesRep]))>0

...where tblCriteria[SalesRep] is a list of names to be included in the filter.

Please see the attached workbook, which also contains two MS365-only examples, using LET, FILTER and a few other dynamic array functions.

[#10]

@rlclynn 

= LET(
    nonblanks, FILTER(selected, ISTEXT(selected)),
    criterion, BYROW(SalesPeople, LAMBDA(names, OR(names=nonblanks))),
    FILTER(Table1, criterion)
  )

The first line guards against the list of selected salespeople containing blank fields.

The second takes the fields containing the names of salespeople associated with the transaction and compares them to the list of non-blank selections to give a 2D array.  If there is a match the criterion for that record will be set to TRUE.

Finally, the table is filtered.

[#11]

@rlclynn 

https://e.anyoupin.cn/EData/1241.html

//select * from filter_multi_columns;
xli_add_php~～
if(!empty($_POST["tbl"])){
//print_r($_POST);
$tblrowid=implode(',',$_POST["tbl"]);
echo $tblrowid;
$sql=<<<eof
select * from filter_multi_columns where (instr("$tblrowid",f04)>0 and f04 not like "") or (instr("$tblrowid",f05)>0 and f05 not like "");
eof;

//echo($sql);
\multiquery\multiquery_run($sql);

}
?>
<form action="" method=post>
～;
select * from (
select f04,'<input type=checkbox name="tbl[]" value='||f04||'>' options from filter_multi_columns group by f04 union 
select f05,'<input type=checkbox name="tbl[]" value='||f05||'>' 选择 from filter_multi_columns group by f05) where f04 not like '';
;

xli_add_php~～
?>
<button>submit</button>
</form>
～;