Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Unanswered: Checking for a subset

    In Excel how can I automatically find out whether or not the 3 characters in cells N2:P2 are a subset of the 4 characters in cells J2:M2?
    The characters are a mixture of text and numbers.
    I would like a formula that I could copy down (perhaps giving TRUE or FALSE in cell Q2).

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    by subset what do you mean?
    they are a subset of the same values
    they contain some of the same characters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52
    Each of the 3 characters in cells N2:P2 is to be found in the cells J2:M2.
    Thus is a, a, 5 are in N2:P2 and J2:M2 contains 5, 6, a, a we get TRUE but if J2:M2 contains 5, 6, a, b we get FALSE.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this looks like a nice problem for regex, but I don't do regex (and as far as I can tell, Excel doesn't either).

    the following function
    - is generalised a bit to cover an arbitrary number of cells in masterset and candidatesubset
    - has no error checking or parameter validation
    - is not very pretty
    - works (...if I understood the rules correctly)

    izy

    Code:
    Function subset(CandidateSubset As Range, MasterSet As Range) As Boolean
    
        'Function to return TRUE if CandidateSubset can be constructed (with rules) from MasterSet, else FALSE
        
        'Rules:
        '   if MasterSet contains 'abcdee', then return
        '       TRUE with CandidateSubset 'aee' (one 'a' and two 'e' are available in MasterSet)
        '       FALSE with CandidateSubset 'abb' (second 'b' not available in MasterSet)
        '       FALSE with CandidateSubset 'abx' ('x' not available in MasterSet)
        
        'Function assumes (but does not verify) that:
        '   CandidateSubset and MasterSet ranges are single-row ranges (tested ranges up to 10 columns wide)
        '   Cells in each range contain a single 'character' (tested [a-z],[0-9] only)
        '   NB no tests conducted on multi-row ranges or multi-character cells
    
        Dim tgt As Integer      'number of columns in CandidateSubset ("target" chars still to be matched)
        Dim ofs As Integer      'column-number of first column in MasterSet ("offset")
        Dim mst As Integer      'number of columns in MasterSet (for used() array)
        Dim used() As Boolean   'TRUE if MasterSet element(x) already used (cf Rules)
        
        tgt = CandidateSubset.Columns.Count
        mst = MasterSet.Columns.Count
        
        '// ISSUE this works but there must be a better way to find offset to first column in range
        For Each d In MasterSet                 'this
            ofs = d.Column                      'is
            Exit For                            'very
        Next                                    'ugly
        
        ReDim used(mst)                         'Array is one element too big (0 to mst). who cares!
        
        For i = LBound(used) To UBound(used)    'FALSE is likely the default but
            used(i) = False                     'Excel help is so horribly ugly
        Next                                    'that I can't be bothered with it
            
        For Each c In CandidateSubset
            For Each d In MasterSet
                If (c.Value = d.Value) And (Not used(d.Column - ofs)) Then  'matched and not used
                    used(d.Column - ofs) = True                             'prevent re-use
                    tgt = tgt - 1                                           'getting closer to target
                    Exit For                                                'candidate OK so skip remaining masters
                End If
            Next 'master
        Next 'candidate
        
        If tgt = 0 Then
            subset = True
        Else
            subset = False
        End If
    
    End Function
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52
    Hello izyrider

    It's very kind of you to reply to this problem. You may remember helping me with Access questions in the past. I found a way to check for this subset in Excel but without VBA:
    =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2&K2&L2&M2, N2,""),O2,""),P2,""))<=1

    However your code is excellent, as ever.

    John

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi John,

    sure I remember providing several complicated solutions: you found something simpler each time. here we go again

    in my defence: your formula is going to look rather ugly by the time the candidate substring hits 100 cells.

    it was fun though.

    HAPPY NEW YEAR
    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •