Searching for numbers within cells

[extrudebg]

This may be a complete longshot...I realize that. This is a completely simplified version of my situation.

I have 2 cells that contain multiple three digit numbers seperated by a space. Example:

Cell A1: 001 002 003 005 006 009 (all in one cell)
Cell A2: 002 003 004 005 006 007 (all in one cell)

I'm trying to find a way to verify if each three digit number is present in the other cell.

001 and 009 are in cell A1 and not A2
004 and 007 are in cell A2 and not A1
002 003 005 006 are in both

Thanks.

[Trebor76]

Hi extrudebg,

If this is a simplified version of what you need, then I'd say VBA will be the way to go though based on your sample data the following formula will do the job:

=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,2)))

HTH

Robert

[oldchippy]

Hi,

May be something like this?

[extrudebg]

Robert...that formula works great on the defined paramaters. Is there any way to set that up to balance any three digit combination...not only known values? Also, it's more important that I know what numbers aren't included...is that a possible output?

[extrudebg]

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.

[Trebor76]

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

[extrudebg]

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

[Trebor76]

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

[jindon]

This may be a complete longshot...I realize that. This is a completely simplified version of my situation.

I have 2 cells that contain multiple three digit numbers seperated by a space. Example:

Cell A1: 001 002 003 005 006 009 (all in one cell)
Cell A2: 002 003 004 005 006 007 (all in one cell)

I'm trying to find a way to verify if each three digit number is present in the other cell.

001 and 009 are in cell A1 and not A2
004 and 007 are in cell A2 and not A1
002 003 005 006 are in both

Thanks.

UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) Hit Alt + F11 again
Select 2 horizontal 3 consecutive cells (e.g. B1:D1) and enter
=extrude(A1,A2)
Then confirm with Ctrl + Shift + Enter (Array formula entry)

Function extrude(txt1 As String, txt2 As String) As Variant
Dim TopOnly As String, BottonOnly As String, inBoth As String
With CreateObject("Scripting.Dictionary")
    For Each e In Split(Application.Trim(txt1))
        If Not .exists(e) Then .add e, Nothing
    Next
    For Each e In Split(Application.Trim(txt2))
        If .exists(e) Then
            inBoth = inBoth & IIf(inBoth = "",""," ") & e
            .remove(e)
        Else
            BottomOnly = BottmOnly & IIf(BottmOnly = "",""," ") & e
        End If
    Next
    If .Count > 0 Then TopOnly = Join(.keys)
End With
extrude = Array(TopOnly, BottomOnly, inBoth)
End Function