Finding text strings in complex situations

Hello,

I need to find all the text strings, which matches to certain criteria.
Briefly, I have a criterium "test". Then, I'm interested in the rows which
have "test" string in a column B. I'm looking for the text strings in the
column C, which correspond the rows according to "test" criterium. I have to
eliminate all the duplicate and blank values and finally concatenate rest of
the values.

VLOOKUP just returns the first match, which can even be blank. In theory, I
could use the OFFSET and MATCH -functions for all the matches, changing the
reference to correspond the next row from the previous match and continue.
However, there are so many matches that it would be impossible to link so
many loops.

DGET would be an ideal function, only if it could handle the multiple
matches. If there was an IS-function to #NUM!- and #VALUE errors
particularly, I could determine, if there are matches for the criteria.

Array functions don't seem to solve this problem either, because the rows
which corresponds the criterium are in disorder, so I can't get an array,
which would correspond the criterium.

If I understand you correctly, you want to identify all records where the
word "test" appears in Column B AND Column C. Alternatively, you may be
looking for instances where the word "test" appears in Column B AND other,
specific words in Column C.

If you could clarify the position I will try to help.

Regards.

Bill Ridgeway
Computer Solutions

"Hardel" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I need to find all the text strings, which matches to certain criteria.
> Briefly, I have a criterium "test". Then, I'm interested in the rows which
> have "test" string in a column B. I'm looking for the text strings in the
> column C, which correspond the rows according to "test" criterium. I have
> to
> eliminate all the duplicate and blank values and finally concatenate rest
> of
> the values.
>
> VLOOKUP just returns the first match, which can even be blank. In theory,
> I
> could use the OFFSET and MATCH -functions for all the matches, changing
> the
> reference to correspond the next row from the previous match and continue.
> However, there are so many matches that it would be impossible to link so
> many loops.
>
> DGET would be an ideal function, only if it could handle the multiple
> matches. If there was an IS-function to #NUM!- and #VALUE errors
> particularly, I could determine, if there are matches for the criteria.
>
> Array functions don't seem to solve this problem either, because the rows
> which corresponds the criterium are in disorder, so I can't get an array,
> which would correspond the criterium.

Thanks for the answer.

Actually, I have a few other criteria in other columns, but it's not
important. You could imagine I only have one criterion in column B. I'd like
to get all the values in column C returned. So, for example:

Column B | Column C
oiasdfj | joko
test | correct match
test | another correct match
spjas | spdo
test |
test | correct match

In this case, I would like to get the result: correct match / another
correct match

So, the function should remove the double match: "correct match" and the
empty cell, which actually contains the reference. The reference in that cell
doesn't return a value, but Excel don't think it's an empty cell because of
the reference. I can't make the difference between the "empty" cell and teh
other cells because Excel thinks both of them contains text and none of them
contains number (ISTEXT, ISNUMBER).

"Bill Ridgeway" wrote:

> If I understand you correctly, you want to identify all records where the
> word "test" appears in Column B AND Column C. Alternatively, you may be
> looking for instances where the word "test" appears in Column B AND other,
> specific words in Column C.
>
> If you could clarify the position I will try to help.
>
> Regards.
>
> Bill Ridgeway
> Computer Solutions
>
> "Hardel" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I need to find all the text strings, which matches to certain criteria.
> > Briefly, I have a criterium "test". Then, I'm interested in the rows which
> > have "test" string in a column B. I'm looking for the text strings in the
> > column C, which correspond the rows according to "test" criterium. I have
> > to
> > eliminate all the duplicate and blank values and finally concatenate rest
> > of
> > the values.
> >
> > VLOOKUP just returns the first match, which can even be blank. In theory,
> > I
> > could use the OFFSET and MATCH -functions for all the matches, changing
> > the
> > reference to correspond the next row from the previous match and continue.
> > However, there are so many matches that it would be impossible to link so
> > many loops.
> >
> > DGET would be an ideal function, only if it could handle the multiple
> > matches. If there was an IS-function to #NUM!- and #VALUE errors
> > particularly, I could determine, if there are matches for the criteria.
> >
> > Array functions don't seem to solve this problem either, because the rows
> > which corresponds the criterium are in disorder, so I can't get an array,
> > which would correspond the criterium.
>
>
>

Can't you apply an autofilter to column B for "test" and then a custom
filter to C for "Not blank" or Contains... "match"?

Hope this helps.

Pete

Hardel wrote:
> Thanks for the answer.
>
> Actually, I have a few other criteria in other columns, but it's not
> important. You could imagine I only have one criterion in column B. I'd like
> to get all the values in column C returned. So, for example:
>
> Column B | Column C
> oiasdfj | joko
> test | correct match
> test | another correct match
> spjas | spdo
> test |
> test | correct match
>
> In this case, I would like to get the result: correct match / another
> correct match
>
> So, the function should remove the double match: "correct match" and the
> empty cell, which actually contains the reference. The reference in that cell
> doesn't return a value, but Excel don't think it's an empty cell because of
> the reference. I can't make the difference between the "empty" cell and teh
> other cells because Excel thinks both of them contains text and none of them
> contains number (ISTEXT, ISNUMBER).
>
> "Bill Ridgeway" wrote:
>
> > If I understand you correctly, you want to identify all records where the
> > word "test" appears in Column B AND Column C. Alternatively, you may be
> > looking for instances where the word "test" appears in Column B AND other,
> > specific words in Column C.
> >
> > If you could clarify the position I will try to help.
> >
> > Regards.
> >
> > Bill Ridgeway
> > Computer Solutions
> >
> > "Hardel" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > I need to find all the text strings, which matches to certain criteria.
> > > Briefly, I have a criterium "test". Then, I'm interested in the rows which
> > > have "test" string in a column B. I'm looking for the text strings in the
> > > column C, which correspond the rows according to "test" criterium. I have
> > > to
> > > eliminate all the duplicate and blank values and finally concatenate rest
> > > of
> > > the values.
> > >
> > > VLOOKUP just returns the first match, which can even be blank. In theory,
> > > I
> > > could use the OFFSET and MATCH -functions for all the matches, changing
> > > the
> > > reference to correspond the next row from the previous match and continue.
> > > However, there are so many matches that it would be impossible to link so
> > > many loops.
> > >
> > > DGET would be an ideal function, only if it could handle the multiple
> > > matches. If there was an IS-function to #NUM!- and #VALUE errors
> > > particularly, I could determine, if there are matches for the criteria.
> > >
> > > Array functions don't seem to solve this problem either, because the rows
> > > which corresponds the criterium are in disorder, so I can't get an array,
> > > which would correspond the criterium.
> >
> >
> >

Unfortunately, filters aren't convenient because I don't have a permission to
change the "database" document. I just have to search the information from
that file according the criteria. Certainly, I could filter the document,
then copy and paste as values, but this is all I am trying to get rid of: to
avoid manual work, because I get new versions all the time.

"Pete_UK" wrote:

> Can't you apply an autofilter to column B for "test" and then a custom
> filter to C for "Not blank" or Contains... "match"?
>
> Hope this helps.
>
> Pete
>
> Hardel wrote:
> > Thanks for the answer.
> >
> > Actually, I have a few other criteria in other columns, but it's not
> > important. You could imagine I only have one criterion in column B. I'd like
> > to get all the values in column C returned. So, for example:
> >
> > Column B | Column C
> > oiasdfj | joko
> > test | correct match
> > test | another correct match
> > spjas | spdo
> > test |
> > test | correct match
> >
> > In this case, I would like to get the result: correct match / another
> > correct match
> >
> > So, the function should remove the double match: "correct match" and the
> > empty cell, which actually contains the reference. The reference in that cell
> > doesn't return a value, but Excel don't think it's an empty cell because of
> > the reference. I can't make the difference between the "empty" cell and teh
> > other cells because Excel thinks both of them contains text and none of them
> > contains number (ISTEXT, ISNUMBER).
> >
> > "Bill Ridgeway" wrote:
> >
> > > If I understand you correctly, you want to identify all records where the
> > > word "test" appears in Column B AND Column C. Alternatively, you may be
> > > looking for instances where the word "test" appears in Column B AND other,
> > > specific words in Column C.
> > >
> > > If you could clarify the position I will try to help.
> > >
> > > Regards.
> > >
> > > Bill Ridgeway
> > > Computer Solutions
> > >
> > > "Hardel" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hello,
> > > >
> > > > I need to find all the text strings, which matches to certain criteria.
> > > > Briefly, I have a criterium "test". Then, I'm interested in the rows which
> > > > have "test" string in a column B. I'm looking for the text strings in the
> > > > column C, which correspond the rows according to "test" criterium. I have
> > > > to
> > > > eliminate all the duplicate and blank values and finally concatenate rest
> > > > of
> > > > the values.
> > > >
> > > > VLOOKUP just returns the first match, which can even be blank. In theory,
> > > > I
> > > > could use the OFFSET and MATCH -functions for all the matches, changing
> > > > the
> > > > reference to correspond the next row from the previous match and continue.
> > > > However, there are so many matches that it would be impossible to link so
> > > > many loops.
> > > >
> > > > DGET would be an ideal function, only if it could handle the multiple
> > > > matches. If there was an IS-function to #NUM!- and #VALUE errors
> > > > particularly, I could determine, if there are matches for the criteria.
> > > >
> > > > Array functions don't seem to solve this problem either, because the rows
> > > > which corresponds the criterium are in disorder, so I can't get an array,
> > > > which would correspond the criterium.
> > >
> > >
> > >
>
>

If you don't have permission to change the spreadsheet, can you highlight
the whole of the spreadsheet (Click on the square immediately at the
north-west corner) and paste it a new spreadsheet and work on it there?

Regards.

Bill Ridgeway
Computer Solutions

"Hardel" <[email protected]> wrote in message
news:[email protected]...
> Unfortunately, filters aren't convenient because I don't have a permission
> to
> change the "database" document. I just have to search the information from
> that file according the criteria. Certainly, I could filter the document,
> then copy and paste as values, but this is all I am trying to get rid of:
> to
> avoid manual work, because I get new versions all the time.
>
> "Pete_UK" wrote:
>
>> Can't you apply an autofilter to column B for "test" and then a custom
>> filter to C for "Not blank" or Contains... "match"?
>>
>> Hope this helps.
>>
>> Pete
>>
>> Hardel wrote:
>> > Thanks for the answer.
>> >
>> > Actually, I have a few other criteria in other columns, but it's not
>> > important. You could imagine I only have one criterion in column B. I'd
>> > like
>> > to get all the values in column C returned. So, for example:
>> >
>> > Column B | Column C
>> > oiasdfj | joko
>> > test | correct match
>> > test | another correct match
>> > spjas | spdo
>> > test |
>> > test | correct match
>> >
>> > In this case, I would like to get the result: correct match / another
>> > correct match
>> >
>> > So, the function should remove the double match: "correct match" and
>> > the
>> > empty cell, which actually contains the reference. The reference in
>> > that cell
>> > doesn't return a value, but Excel don't think it's an empty cell
>> > because of
>> > the reference. I can't make the difference between the "empty" cell and
>> > teh
>> > other cells because Excel thinks both of them contains text and none of
>> > them
>> > contains number (ISTEXT, ISNUMBER).
>> >
>> > "Bill Ridgeway" wrote:
>> >
>> > > If I understand you correctly, you want to identify all records where
>> > > the
>> > > word "test" appears in Column B AND Column C. Alternatively, you may
>> > > be
>> > > looking for instances where the word "test" appears in Column B AND
>> > > other,
>> > > specific words in Column C.
>> > >
>> > > If you could clarify the position I will try to help.
>> > >
>> > > Regards.
>> > >
>> > > Bill Ridgeway
>> > > Computer Solutions
>> > >
>> > > "Hardel" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > Hello,
>> > > >
>> > > > I need to find all the text strings, which matches to certain
>> > > > criteria.
>> > > > Briefly, I have a criterium "test". Then, I'm interested in the
>> > > > rows which
>> > > > have "test" string in a column B. I'm looking for the text strings
>> > > > in the
>> > > > column C, which correspond the rows according to "test" criterium.
>> > > > I have
>> > > > to
>> > > > eliminate all the duplicate and blank values and finally
>> > > > concatenate rest
>> > > > of
>> > > > the values.
>> > > >
>> > > > VLOOKUP just returns the first match, which can even be blank. In
>> > > > theory,
>> > > > I
>> > > > could use the OFFSET and MATCH -functions for all the matches,
>> > > > changing
>> > > > the
>> > > > reference to correspond the next row from the previous match and
>> > > > continue.
>> > > > However, there are so many matches that it would be impossible to
>> > > > link so
>> > > > many loops.
>> > > >
>> > > > DGET would be an ideal function, only if it could handle the
>> > > > multiple
>> > > > matches. If there was an IS-function to #NUM!- and #VALUE errors
>> > > > particularly, I could determine, if there are matches for the
>> > > > criteria.
>> > > >
>> > > > Array functions don't seem to solve this problem either, because
>> > > > the rows
>> > > > which corresponds the criterium are in disorder, so I can't get an
>> > > > array,
>> > > > which would correspond the criterium.
>> > >
>> > >
>> > >
>>
>>

Yes, I could. But I'm trying to get rid of that kind of manual work. My
target is the solution which could be updated to correspond the new version
just by "updating links".

"Bill Ridgeway" wrote:

> If you don't have permission to change the spreadsheet, can you highlight
> the whole of the spreadsheet (Click on the square immediately at the
> north-west corner) and paste it a new spreadsheet and work on it there?
>
> Regards.
>
> Bill Ridgeway
> Computer Solutions
>
> "Hardel" <[email protected]> wrote in message
> news:[email protected]...
> > Unfortunately, filters aren't convenient because I don't have a permission
> > to
> > change the "database" document. I just have to search the information from
> > that file according the criteria. Certainly, I could filter the document,
> > then copy and paste as values, but this is all I am trying to get rid of:
> > to
> > avoid manual work, because I get new versions all the time.
> >
> > "Pete_UK" wrote:
> >
> >> Can't you apply an autofilter to column B for "test" and then a custom
> >> filter to C for "Not blank" or Contains... "match"?
> >>
> >> Hope this helps.
> >>
> >> Pete
> >>
> >> Hardel wrote:
> >> > Thanks for the answer.
> >> >
> >> > Actually, I have a few other criteria in other columns, but it's not
> >> > important. You could imagine I only have one criterion in column B. I'd
> >> > like
> >> > to get all the values in column C returned. So, for example:
> >> >
> >> > Column B | Column C
> >> > oiasdfj | joko
> >> > test | correct match
> >> > test | another correct match
> >> > spjas | spdo
> >> > test |
> >> > test | correct match
> >> >
> >> > In this case, I would like to get the result: correct match / another
> >> > correct match
> >> >
> >> > So, the function should remove the double match: "correct match" and
> >> > the
> >> > empty cell, which actually contains the reference. The reference in
> >> > that cell
> >> > doesn't return a value, but Excel don't think it's an empty cell
> >> > because of
> >> > the reference. I can't make the difference between the "empty" cell and
> >> > teh
> >> > other cells because Excel thinks both of them contains text and none of
> >> > them
> >> > contains number (ISTEXT, ISNUMBER).
> >> >
> >> > "Bill Ridgeway" wrote:
> >> >
> >> > > If I understand you correctly, you want to identify all records where
> >> > > the
> >> > > word "test" appears in Column B AND Column C. Alternatively, you may
> >> > > be
> >> > > looking for instances where the word "test" appears in Column B AND
> >> > > other,
> >> > > specific words in Column C.
> >> > >
> >> > > If you could clarify the position I will try to help.
> >> > >
> >> > > Regards.
> >> > >
> >> > > Bill Ridgeway
> >> > > Computer Solutions
> >> > >
> >> > > "Hardel" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > Hello,
> >> > > >
> >> > > > I need to find all the text strings, which matches to certain
> >> > > > criteria.
> >> > > > Briefly, I have a criterium "test". Then, I'm interested in the
> >> > > > rows which
> >> > > > have "test" string in a column B. I'm looking for the text strings
> >> > > > in the
> >> > > > column C, which correspond the rows according to "test" criterium.
> >> > > > I have
> >> > > > to
> >> > > > eliminate all the duplicate and blank values and finally
> >> > > > concatenate rest
> >> > > > of
> >> > > > the values.
> >> > > >
> >> > > > VLOOKUP just returns the first match, which can even be blank. In
> >> > > > theory,
> >> > > > I
> >> > > > could use the OFFSET and MATCH -functions for all the matches,
> >> > > > changing
> >> > > > the
> >> > > > reference to correspond the next row from the previous match and
> >> > > > continue.
> >> > > > However, there are so many matches that it would be impossible to
> >> > > > link so
> >> > > > many loops.
> >> > > >
> >> > > > DGET would be an ideal function, only if it could handle the
> >> > > > multiple
> >> > > > matches. If there was an IS-function to #NUM!- and #VALUE errors
> >> > > > particularly, I could determine, if there are matches for the
> >> > > > criteria.
> >> > > >
> >> > > > Array functions don't seem to solve this problem either, because
> >> > > > the rows
> >> > > > which corresponds the criterium are in disorder, so I can't get an
> >> > > > array,
> >> > > > which would correspond the criterium.
> >> > >
> >> > >
> >> > >
> >>
> >>
>
>
>