Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter Query--Not | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) | |||
How to use a query rathen than advanced filter | Excel Programming |