Filtering table info

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DonH - 27 Oct 2006 09:42 GMT

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

Reply to this Message

DonH - 27 Oct 2006 10:06 GMT

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

> Hi,
>
[quoted text clipped - 17 lines]
>
> DonH

Reply to this Message

Max - 27 Oct 2006 11:10 GMT

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

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> PS
>
[quoted text clipped - 8 lines]
>
> DonH

>> Hi,
>>
[quoted text clipped - 17 lines]
>>
>> DonH

Reply to this Message

Max - 27 Oct 2006 11:17 GMT

> (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()))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

DonH - 27 Oct 2006 11:26 GMT

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

Many thanks again.

DonH

>>> I would like to filter out all of the employees who do not meet any of
>>> around 5 different criteria.
[quoted text clipped - 51 lines]
>>>
>>> DonH

Reply to this Message

Max - 27 Oct 2006 11:33 GMT

You're welcome, DonH !

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

Rate this thread: