Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default Advanced filter query

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.

--
See ya,
Ken McLennan
Qld, Australia
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default Advanced filter query

On Feb 11, 2:57 am, Ken McLennan wrote:
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.

--
See ya,
Ken McLennan
Qld, Australia


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 values:(True/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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 63
Default Advanced filter query

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
On Feb 11, 2:57 am, Ken McLennan wrote:
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.

--
See ya,
Ken McLennan
Qld, Australia



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default Advanced filter query

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

--
See ya,
Ken McLennan
Qld, Australia
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Filter Query--Not Orgelizer[_2_] Excel Discussion (Misc queries) 2 August 18th 09 12:40 AM
Why won't advanced filter return filter results? jaws4518 Excel Worksheet Functions 5 September 12th 06 06:11 PM
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du TC Excel Worksheet Functions 1 May 12th 05 02:06 AM
advanced filter won't allow me to filter on bracketed text (-456.2 LucianoG Excel Discussion (Misc queries) 1 December 6th 04 08:38 PM
How to use a query rathen than advanced filter MassimoM Excel Programming 2 November 3rd 04 01:53 PM


All times are GMT +1. The time now is 05:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"