We help IT Professionals succeed at work.

Excel filter across multiple columns

Ok, Excel question....

I've got a 13000 row spreadsheet.  Each row is a unique 'incident' in our school.

Each incident (row) can have up to 5 'action codes' entered...these are 5 different columns (CODE1, CODE2, etc)

There are 9 possible action codes (ESU1 - ESU9)

So I need to look across 5 different columns and then I want to filter out ANY row that does NOT have an action code of ESU1 - ESU9

This will leave me with only incidents that involved a suspension.

I need some help in how to filter across multiple columns.

Hope this is clear.

Thanks a bunch.
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
Possible to derive with a new column with Formula to differentiate the rows?

such as:

=IF( COUNTIF(B2:F2,"<>") - (COUNTIF(B2:F2,"ESU1")+COUNTIF(B2:F2,"ESU2")+COUNTIF(B2:F2,"ESU3")+COUNTIF(B2:F2,"ESU4")+COUNTIF(B2:F2,"ESU5")+COUNTIF(B2:F2,"ESU6")+COUNTIF(B2:F2,"ESU7")+COUNTIF(B2:F2,"ESU8")+COUNTIF(B2:F2,"ESU9")+COUNTIF(B2:F2,"ESU10")) > 0, TRUE, FALSE)

Open in new window

29171674.xlsx

Author

Commented:
Looking at your attached file, seems very close.....

I may have mis-spoke my intent

Using your approach, I want to look at 5 specific columns

If ANY column has an ESU* code, then that row is TRUE

If NO column has an ESU* code, then that row is FALSE

I hope that makes better sense of what I need.

Thanks
Software Team Lead
Commented:
in that case, it would be easier, try like:

=IF( COUNTIF(B2:F2,"<>") - COUNTIF(B2:F2,"ESU*") > 0, TRUE, FALSE)

Open in new window



Count cells that begin with
https://exceljet.net/formula/count-cells-that-begin-with