Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching concatinated fields
Hi there,
I currently have a spreadsheet of several thousand records each of which is categorised into five columns. What I'd like to do is to concatinate the category columns into one field so that I have five values in one cell, split up by a comma or semi-colon (ie "f;t;d;o;l;r"). I then want to search on any instance of those categories So here's the questions: 1) How should I write the string search to look for a particular letter (ie "see if cell C3 contains the letter 'd' ") 2) Would this change if I was searching for a double letter combination (ie 'eg' for egg or, 'do' for dog)? 3) Is there a benefit to spliting up the field with a comma or a semi-colon (or anything else come to that)? 4) Could I achieve this from AdvancedSearch or AutoFilter? (I want the criteria to be selected from a Form that the user can change) The main problem at the moment if the file size's negative effect on performance (particularly whilst using the existing AutoFilter. Thanks in advance John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching concatinated fields
1) In VBA: If Range("CheckCell").value like "*;d;*" Then...
- this lets you see if a d (with ; around it) is in the cell referred to by CheckCell, can be used in various ways to count or make decisions. As an Excel formula, =COUNTIF(A:A,"*;d;*") will count how many times the ";d;" pattern is in column A of your worksheet. Similar formulas will accomplish other things. 2) You can use variations to find combinations: - "*;do;*" only finds "do" together with no other letter before or after (within the given section of the cell contents marked out by the delimiters) - ";*do*" finds "do" together even if another letter follows, i.e. would find ";do;", ";dog;", ";doggone;", ";donut;", ... - "*do*" would also find "do" in the middle of a word, like ";undone;" You can use various combinations of these in various ways. 3) The benefit of the delimiters is in the ability to recognize adjacent letters ONLY within the same 'word' - for example, if you had the words "do" and "good", no delimiters gives "dogood" and you would find the combination "*dog*"; but with the delimiters it is "do;good" and it would not find the combination "*dog*". 4) You could certainly do this in many ways, but I find this technique to be easy and handy when trying to choose criteria from multiple related cells and I use it often. As for performance, a filter is pretty slow, but all the calculations to do the concatenation and searches can also take a while. So there is a tradeoff and you would have to see what works best for you. "John" wrote: Hi there, I currently have a spreadsheet of several thousand records each of which is categorised into five columns. What I'd like to do is to concatinate the category columns into one field so that I have five values in one cell, split up by a comma or semi-colon (ie "f;t;d;o;l;r"). I then want to search on any instance of those categories So here's the questions: 1) How should I write the string search to look for a particular letter (ie "see if cell C3 contains the letter 'd' ") 2) Would this change if I was searching for a double letter combination (ie 'eg' for egg or, 'do' for dog)? 3) Is there a benefit to spliting up the field with a comma or a semi-colon (or anything else come to that)? 4) Could I achieve this from AdvancedSearch or AutoFilter? (I want the criteria to be selected from a Form that the user can change) The main problem at the moment if the file size's negative effect on performance (particularly whilst using the existing AutoFilter. Thanks in advance John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching concatinated fields
Excellent. That's just the kind of advice I was after. Thanks very much
for your time K. "K Dales" wrote in message ... 1) In VBA: If Range("CheckCell").value like "*;d;*" Then... - this lets you see if a d (with ; around it) is in the cell referred to by CheckCell, can be used in various ways to count or make decisions. As an Excel formula, =COUNTIF(A:A,"*;d;*") will count how many times the ";d;" pattern is in column A of your worksheet. Similar formulas will accomplish other things. 2) You can use variations to find combinations: - "*;do;*" only finds "do" together with no other letter before or after (within the given section of the cell contents marked out by the delimiters) - ";*do*" finds "do" together even if another letter follows, i.e. would find ";do;", ";dog;", ";doggone;", ";donut;", ... - "*do*" would also find "do" in the middle of a word, like ";undone;" You can use various combinations of these in various ways. 3) The benefit of the delimiters is in the ability to recognize adjacent letters ONLY within the same 'word' - for example, if you had the words "do" and "good", no delimiters gives "dogood" and you would find the combination "*dog*"; but with the delimiters it is "do;good" and it would not find the combination "*dog*". 4) You could certainly do this in many ways, but I find this technique to be easy and handy when trying to choose criteria from multiple related cells and I use it often. As for performance, a filter is pretty slow, but all the calculations to do the concatenation and searches can also take a while. So there is a tradeoff and you would have to see what works best for you. "John" wrote: Hi there, I currently have a spreadsheet of several thousand records each of which is categorised into five columns. What I'd like to do is to concatinate the category columns into one field so that I have five values in one cell, split up by a comma or semi-colon (ie "f;t;d;o;l;r"). I then want to search on any instance of those categories So here's the questions: 1) How should I write the string search to look for a particular letter (ie "see if cell C3 contains the letter 'd' ") 2) Would this change if I was searching for a double letter combination (ie 'eg' for egg or, 'do' for dog)? 3) Is there a benefit to spliting up the field with a comma or a semi-colon (or anything else come to that)? 4) Could I achieve this from AdvancedSearch or AutoFilter? (I want the criteria to be selected from a Form that the user can change) The main problem at the moment if the file size's negative effect on performance (particularly whilst using the existing AutoFilter. Thanks in advance John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching multiple fields and then print the entire row from resul | Excel Worksheet Functions | |||
How do I keep numbers formatted within a concatinated cell? | Excel Discussion (Misc queries) | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
EXCEL - Concatinated Y-axis Label | Excel Discussion (Misc queries) | |||
VLookup with concatinated named range | Excel Worksheet Functions |