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

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

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