Searching for numbers within cells

oldchippy...i had thought about doing something like that, but the sheer volume of 3 digit combinations (tens of thousands total) I'm delaing with would result in immense data that would still need to be sorted and analyzed. Thanks for your help though.

Hi extrudebg,

Not sure if these will help, but the following formula will return a set of three digits that are not within any part of the string (cell) beneath (i.e. they're unique):

=TRIM(IF(ISERROR(SEARCH(LEFT($A$1,3),$A$2)),LEFT($A$1,3),"")&" " &IF(ISERROR(SEARCH(MID($A$1,5,3),$A$2)),MID($A$1,5,3),"")&" "&IF(ISERROR(SEARCH(MID($A$1,9,3),$A$2)),MID($A$1,9,3),"")&" "&IF(ISERROR(SEARCH(MID($A$1,13,3),$A$2)),MID($A$1,13,3),"")&" "&IF(ISERROR(SEARCH(MID($A$1,17,3),$A$2)),MID($A$1,17,3),"")&" "&IF(ISERROR(SEARCH(RIGHT($A$1,3),$A$2)),RIGHT($A$1,3),""))

While the following formula will only return a set of three digits where their same position in the string (cell) beneath is different (i.e. they're unique):

=TRIM(IF(LEFT($A$1,3)<>LEFT($A$2,3),LEFT($A$1,3),"")&" " &IF(MID($A$1,5,3)<>MID($A$2,5,3),MID($A$1,5,3),"")&" " &IF(MID($A$1,9,3)<>MID($A$2,9,3),MID($A$1,9,3),"")&" " &IF(MID($A$1,13,3)<>MID($A$2,13,3),MID($A$1,13,3),"")&" " &IF(MID($A$1,17,3)<>MID($A$2,17,3),MID($A$1,17,3),"")&" " &IF(RIGHT($A$1,3)<>RIGHT($A$2,3),RIGHT($A$1,3),""))

HTH

Robert

Robert...that first formula is almost exactly what I was looking for. I do have one question.

When I place the formula in my my spreadhseet it calculates the missing sets of numbers but will only give a maximum of 6 results. Some comparison sets may have considerably more. Is there any way to make the output result and indefinite number of sets?

thanks,

dennis

Hi Dennis,

Yes, I set the formula based on your original data to return a maximum of 6 x 3 set of (unique) results. While the formula can be extended to some degree, I think Excel's formula limit if 1,024 characters in any one will cell will kick in before "the output result for an indefinite number of sets" is possible.

Perhaps someone out there has created a "User Defined Function" (UDF) to handle this (or a very) similar situation though the indefinite part is always going to problematic.

Good luck with your search

Robert

Thanks for your assistance.