Advanced filter query

[Ken McLennan]

G'day there One & All,

Since I can spell XL, I've been nominated as the office guru. I
was asked to modify a spreadsheet (which I wrote, so that's probably the
reason they asked ME). Naturally when I asked I displayed enormous
confidence telling the supervisor "Sure! That's easy!!". Naturally, that
was followed by a list of further modifications which he expected I
could also make with similar ease. Naturally I didn't disuade him from
his belief in my genius, but I did begin to go cross-eyed as the list of
modifications continued growing!


Nearly all were simple, and have been completed. I'm now working
on what I think is the last one, but I'm not exactly sure how to go
about it.

I have an employee list with a header row, that also includes 4
hidden "helper" columns. It can be filtered by values selected in
different comboboxes that work on either the main, or the "helper",
columns to perform the obvious task.

The modification that I told the boss was an easy one (Note to
self: "Keep mouth shut!!") is to further filter the list by values that
are included within the data. For instance, included in the Name column
are various extra details as follows:

Name
-------------------------
Smiff A.B. (STO)(BD)(MCC)
Black C.D.
Jones E.F. (BD)(FW)
Brown G.H. (CU)(OTK)

The extra bits are only vaguely standard, insofar as Makes Crap
Coffee (MCC) might be written as (CCM) - Crap Coffee Maker.

(STO) - Staff Training Officer
(BD) - Beer Drinker
(FW) - Fire Warden (goes well with the beer drinker tag)
(CU) - Completely useless
(OTK) - Overtime King

The meanings and codes are irrelevent. I need to collect each of
them, copy to a work area, make a list of unique entries and then super
glue those entries into a combobox. That part actually IS easy.

My problem now is to filter the list. I can set aside further
helper columns, but since some entries will have more than one code I
can't just put them all in one helper column. Especially since I need to
search unique entries from the combobox. I know that I can put a marker
into a helper cell and then filter the rows with that marker, but that
will show all the entries with codes, not just all the beer drinkers.
I've no doubt that this problem has been addressed before, but Google
brings up squillions of entries. I'm currently working through Mr
Excel's archives, and some posts look helpful, but I'm getting a
headache and can't seem to simplify my problem.

Does anyone have any ideas where I can look? or how to approach
the problem? Is it possible to filter on included strings somehow,
rather than the entire cell contents? (I don't think so. I think a
helper column will somehow be the way to go.)

Thanks for reading this far.

 

[Mark]

G'day there One & All,

Since I can spell XL, I've been nominated as the office guru. I
was asked to modify a spreadsheet (which I wrote, so that's probably the
reason they asked ME). Naturally when I asked I displayed enormous
confidence telling the supervisor "Sure! That's easy!!". Naturally, that
was followed by a list of further modifications which he expected I
could also make with similar ease. Naturally I didn't disuade him from
his belief in my genius, but I did begin to go cross-eyed as the list of
modifications continued growing!

Nearly all were simple, and have been completed. I'm now working
on what I think is the last one, but I'm not exactly sure how to go
about it.

I have an employee list with a header row, that also includes 4
hidden "helper" columns. It can be filtered by values selected in
different comboboxes that work on either the main, or the "helper",
columns to perform the obvious task.

The modification that I told the boss was an easy one (Note to
self: "Keep mouth shut!!") is to further filter the list by values that
are included within the data. For instance, included in the Name column
are various extra details as follows:

Name
-------------------------
Smiff A.B. (STO)(BD)(MCC)
Black C.D.
Jones E.F. (BD)(FW)
Brown G.H. (CU)(OTK)

The extra bits are only vaguely standard, insofar as Makes Crap
Coffee (MCC) might be written as (CCM) - Crap Coffee Maker.

(STO) - Staff Training Officer
(BD) - Beer Drinker
(FW) - Fire Warden (goes well with the beer drinker tag)
(CU) - Completely useless
(OTK) - Overtime King

The meanings and codes are irrelevent. I need to collect each of
them, copy to a work area, make a list of unique entries and then super
glue those entries into a combobox. That part actually IS easy.

My problem now is to filter the list. I can set aside further
helper columns, but since some entries will have more than one code I
can't just put them all in one helper column. Especially since I need to
search unique entries from the combobox. I know that I can put a marker
into a helper cell and then filter the rows with that marker, but that
will show all the entries with codes, not just all the beer drinkers.
I've no doubt that this problem has been addressed before, but Google
brings up squillions of entries. I'm currently working through Mr
Excel's archives, and some posts look helpful, but I'm getting a
headache and can't seem to simplify my problem.

Does anyone have any ideas where I can look? or how to approach
the problem? Is it possible to filter on included strings somehow,
rather than the entire cell contents? (I don't think so. I think a
helper column will somehow be the way to go.)

Thanks for reading this far.

It sounds to me as though you have a design issue. I have learned
that whenever I am working with Excel as a database tool, I must
ALWAYS consider the design of my sheet before I bull ahead with its
development. I think what you need to do is create new "helper
columns" or as they are more commonly known as FIELDS in your
database. Create one column for each of the values you wish to sort
by, ie: an STO column, BD, FW etc. I would then use the Data
Validation feature to insure that each entry in these columns could
only contain one of two valuesTrue/False);(Yes/No);(x/y),etc. Once
you have done that, you will be able to sort by any of the
aformentioned attributes. The only other way to go about this is
through a macro, but it doesnt sound to me as though that is in your
realm of ability just yet.

Hope that helped, if you have any other questions feel free to email
me, I'd be happy to assist you; after all, I have been in EXACTLY your
position before.

Mark

 

[Carl Hartness]

If your list is in A4:A7 and you want the list of acronyms to start at
C4, try
Sub ExtractAcronyms()
Dim inAry, inPtr%, outAry, outPtr%, s$, sPtr%
' load array from range
inAry = Range("A4:A7")
ReDim outAry(1 To 1000, 1 To 1)
outPtr% = 0
' extract (acronym)
For inPtr% = LBound(inAry, 1) To UBound(inAry, 1)
s$ = inAry(inPtr%, 1)
While InStr(1, s$, "(") > 0
outPtr% = outPtr% + 1
' discard preceding text
s$ = Mid(s$, InStr(1, s$, "("))
' find end of acronym
sPtr% = InStr(1, s$, ")")
' save (acronym)
outAry(outPtr%, 1) = Left(s$, sPtr%)
' discard (acronym)
s$ = Mid(s$, sPtr% + 1)
Wend
Next inPtr%
' paste array to range
With Range("C4")
Range(.Cells, .Cells(outPtr%, 1)) = outAry
End With
End Sub

Carl

 

[Ken McLennan]

G'day there Carl,


Sorry to take so long responding to this. Been away from the
'pooter for a bit.

If your list is in A4:A7 and you want the list of acronyms to start at
C4, try

Thanks very much for that. I did get some code working (sort of),
but yours looks easier to follow. I'll have a play with it and I can't
see why it wouldn't do the trick nicely.

Thanks once again