Compare String in Cell to an Array
 

I am wanting to take a string in a cell containing multiple data elements to
an array and have the data in the cell either trimmed or to highlight the
element that is matched in the array.

Example:

Cell B2 contains:
MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

Cells AA2:AA10 contain the array for comparison.

I am not sure where to proceed from here since this appears a bit complex
from the onset. Thanks in advance for any insights.

Compare String in Cell to an Array
 

Hi,
What kind of comparison, what should the result be?
1- Return true if at least one element of B2 is found in AA2:AA10,
2- or Return an array of all elements of B2 found in AA2:AA10
3- or return a pipe (|) delimited string with all elements of B2 found in
AA2:AA10
4- or return the cells of AA2:AA10 having an matching element in B2
....
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"jbtenor1" wrote:

I am wanting to take a string in a cell containing multiple data elements to
an array and have the data in the cell either trimmed or to highlight the
element that is matched in the array.

Example:

Cell B2 contains:
MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

Cells AA2:AA10 contain the array for comparison.

I am not sure where to proceed from here since this appears a bit complex
from the onset. Thanks in advance for any insights.

Compare String in Cell to an Array
 

Use the Find (case sensitive) or Search functions (case insensitive). These
are worksheet functions. See Excel help for details.

--
Regards,
Tom Ogilvy


"jbtenor1" wrote:

I am wanting to take a string in a cell containing multiple data elements to
an array and have the data in the cell either trimmed or to highlight the
element that is matched in the array.

Example:

Cell B2 contains:
MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

Cells AA2:AA10 contain the array for comparison.

I am not sure where to proceed from here since this appears a bit complex
from the onset. Thanks in advance for any insights.

Compare String in Cell to an Array
 

I believe the best result would be for it to return the value(s) that match
one of the elementos in AA2:AA10. The majority of the time, it will just be
one, but it could be two values.

From the example below, if I could have B2 contain the string and a formula
in C2, C2 would give me the result of the match. For instance, AA2:AA10
contains:

ACLEMON
BDUFOUR
BLANGLI
DDILUCE
DWELLS
ESCOTT
KRENKER
THUMENI
WROCHES

If B2 contains:

MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

C2 should return a result of BLANGLI. If there is more than one piece of
matching data, then it would be great if C2 displayed the multiple items in a
new pipe-delimited string. Then, I want to do this for the remaining cells
(B3 to B146) in a successive list. Hopefully, I've been a little more
succinct in my request this time. =)

Thanks again!

"sebastienm" wrote:

Hi,
What kind of comparison, what should the result be?
1- Return true if at least one element of B2 is found in AA2:AA10,
2- or Return an array of all elements of B2 found in AA2:AA10
3- or return a pipe (|) delimited string with all elements of B2 found in
AA2:AA10
4- or return the cells of AA2:AA10 having an matching element in B2
...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"jbtenor1" wrote:

I am wanting to take a string in a cell containing multiple data elements to
an array and have the data in the cell either trimmed or to highlight the
element that is matched in the array.

Example:

Cell B2 contains:
MROMANO | MTELLIG | JGURECK | MWOLK | LGAZDA | GVANBUH | CSHOAF | STILLER |
RVERBIC | DSTEPHE | BLANGLI

Cells AA2:AA10 contain the array for comparison.

I am not sure where to proceed from here since this appears a bit complex
from the onset. Thanks in advance for any insights.