Help refining a formula - not sure if it needs to VBA or not.

[Jveto]

Hey guys,

Back again.

I wanted to try and refine this formula, because it works, but can produce errors pretty easily.

I have two lists of data. Column A can have multiple values in a cell. Column B will have lots of matching values in Column A, but only one value per cell.

This is my helper formula. I put this in column E. I need to hit ctrl+shift+enter.

Please Login or Register  to view this content.

Then I also use this formula in column C. Also ctrl+shift+enter.

Please Login or Register  to view this content.

What they do in combination is number the values in column B based on when they are seen in Column A.

The only problem with this is it looks for ANY matching case. You will see in my example that D1 (column B, row 33) is seen as the same as FD1 (column A, row 12). The same is true for R1 (column B row 48) being seen the same as VR1 (column A, row 36).
How can I make this so it searches for EXACT cases only? So far my workaround has been to scan the list for data that might be similar and replace it with a different name (FD1 -> FN1) Unfortunately I easily miss stuff when I do that.

[CAntosh]

Maybe add a preceding space? Changing the formula in E2 to:

{=MAX(ISNUMBER(SEARCH(" "&B2&","," "&$A$2:$A$84))*ROW($A$2:$A$84))}

seems to fix your false positives?

[Jveto]

Hmm...Ya that seems to work.

So is that saying that _R1, does not equal _VR1?

But R1, does equal VR1, ???

[CAntosh]

Pretty much. The original formula returns a match for any occurrence of "R1," regardless of what is immediately before or after, so things like "VR1," count, as would something like "R1,X". The new version looks specifically for a space followed by R1, so it won't return a match unless it sees " R1,". Given that your data in column A precedes every entry with a space and follows it with a comma, this should be a safe way to look for matches.

[Jveto]

Gotcha, makes sense. I guess I was a little thrown off because there are actually no blank spaces before any of the first entries in column A.
Thanks!

[CAntosh]

there are actually no blank spaces before any of the first entries in column A.

You're absolutely right, but changing the range portion of the SEARCH from $A$2:$A$84 to " "&$A$2:$A$84 addressed that concern. For the purposes of the SEARCH, we put a space in front of each entry in A, so the formula will still work on the first term in each cell in A.

Good luck!