Filtering table info

  • Thread starter Thread starter DonH
  • Start date Start date
D

DonH

Hi,

I have a table of which lists 50 employees and their skills. Cell codes ( D
= Day, 24 = 24 hr cover etc etc etc) are used to show if they are at work
and if so what type of shift they are on. The table logs a months work
rotas at a time and can be very difficult to follow.

Out of the 50 names only 10 will be at the location I'm interested in on any
given day.

I would like to filter out all of the employees who do not meet any of
around 5 different criteria.

I have looked at autofilter but you can only include 2 criteria per column.

Hope you can help

Thanks in anticipation

DonH
 
PS

Just thought I would add that the 5 different criteria mentioned in my first
post gives me the 10 names of people on duty.

So I am just wanting a single new table which only shows these people.

Hope that helps


Many thanks

DonH
 
I would like to filter out all of the employees who do not meet any of
One way ..

Assume the names are in A2 down, codes in B2 down
Assume the 5 "exclusion" codes are: D, E, 24, K, L
(the "24" is assumed a real number, not a text number in col B)

Put in say, E2:
=IF(B2="","",IF(ISNUMBER(MATCH(B2,{"D";"E";24;"K";"L"},0)),"",ROW()))
(Leave E1 empty)

Put in F2:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROW(A1))))

Select E2:F2, fill down to cover the max expected extent of data in cols A
and B. Hide away col E. Col F will return the required results, ie the list
of names in col A whose codes in col B are not amongst the 5 "exclusion"
codes, with all results neatly bunched at the top. Adapt to suit.
 
(the "24" is assumed a real number, not a text number in col B)

If the 24 is a text number in col B, just enclose it with quotes in the
criteria formula, viz:

Put instead in E2:
=IF(B2="","",IF(ISNUMBER(MATCH(B2,{"D";"E";"24";"K";"L"},0)),"",ROW()))

---
 
Many thanks Max it worked well, might need to ask some more though as I try
and integrate it.

Many thanks again.

DonH
 
Back
Top