Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting exact matches and mismatches (2000 SP3)

    I admit I haven't done a search for this - beacuse I don't know what to search for.

    I have a large sheet (over 2000 rows) which includes 8 columns of text data:


    <table border=1><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>BB</td><td>BB</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>AA</td><td>BB</td><td>CC</td><td>AA</td><td>AA</td><td>DD</td><td></td><td></td><td></td><td></td><td></td><td></td></table>
    I need to count how many times the text is exactly the same, and when it isn't how many permutations of each combination occurs. In the above example 1 get (AA only)=1, (AA+BB)=1, (AA+BB+CC)=1 and (AA+DD)=1. (Note that not all 8 columns will always contain data - but the columns are filled from the left, blank cells will be on the right).

    I looked at a number of forumla, including EXACT - but that works only on two cells.

    Any ideas. <img src=/S/please.gif border=0 alt=please width=31 height=23> <img src=/S/broke.gif border=0 alt=broke width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting exact matches and mismatches (2000 SP3)

    The macro below should do what you want.
    Important: the macro modifies the data, so run it on a copy of the data.
    The macro assumes that the data start in A1, and that columns to the right of column H (the 8th column) are blank.
    The last part is rather brute force, it could be made more generic.
    I have attached a workbook with some sample data and the macro.

    Sub CountCombinations()
    Dim lngCol As Long
    Dim lngMaxRow As Long
    Dim lngRow As Long
    lngMaxRow = Range("A65536").End(xlUp).Row
    ' Part 1 - unique items in each row
    For lngRow = 1 To lngMaxRow
    Range(Cells(lngRow, 1), Cells(lngRow, 8)).Sort _
    Key1:=Cells(lngRow, 1), Header:=xlNo, Orientation:=xlLeftToRight
    lngCol = 1
    Do While Not Cells(lngRow, lngCol) = ""
    Do While Cells(lngRow, lngCol) = Cells(lngRow, lngCol + 1)
    Cells(lngRow, lngCol).Delete Shift:=xlShiftToLeft
    Loop
    lngCol = lngCol + 1
    Loop
    Next lngRow
    ' Part 2 - sort rows
    With Range(Cells(1, 1), Cells(lngMaxRow, 8))
    .Sort Key1:=Range("G1"), Key2:=Range("H1"), _
    Header:=xlNo, Orientation:=xlTopToBottom
    .Sort Key1:=Range("D1"), Key2:=Range("E1"), Key3:=Range("F1"), _
    Header:=xlNo, Orientation:=xlTopToBottom
    .Sort Key1:=Range("A1"), Key2:=Range("B1"), Key3:=Range("C1"), _
    Header:=xlNo, Orientation:=xlTopToBottom
    End With
    ' Part 3 - unique rows
    lngRow = 1
    Do While Not Cells(lngRow, 1) = ""
    Cells(lngRow, 9) = 1
    Do While Cells(lngRow, 1) = Cells(lngRow + 1, 1) And _
    Cells(lngRow, 2) = Cells(lngRow + 1, 2) And _
    Cells(lngRow, 3) = Cells(lngRow + 1, 3) And _
    Cells(lngRow, 4) = Cells(lngRow + 1, 4) And _
    Cells(lngRow, 5) = Cells(lngRow + 1, 5) And _
    Cells(lngRow, 6) = Cells(lngRow + 1, 6) And _
    Cells(lngRow, 7) = Cells(lngRow + 1, 7) And _
    Cells(lngRow, 8) = Cells(lngRow + 1, 8)
    Rows(lngRow + 1).Delete
    Cells(lngRow, 9) = Cells(lngRow, 9) + 1
    Loop
    lngRow = lngRow + 1
    Loop
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting exact matches and mismatches (2000 SP3)

    Hans
    Thank you !!! I'll try this when I get to the office tomorrow!

    Les

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting exact matches and mismatches (2000 SP

    Hans, thank you <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>.

    I do get a few 'duplicates' - ie

    AA BB = 5
    AA BB = 2

    However the total numer of records stays the same - and from examining the original data - the total of the duplicates seems right.

    I'm attaching my test file, if you are interested.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting exact matches and mismatches (2000 SP

    The problem arises because the empty-seeming cells (at least some of them, I didn't check all) contain spaces. Excel doesn't consider a cell containing spaces to be empty, even if it looks that way to us.
    If you run the following macro before running the one I posted, you should get unique results.

    Sub RemoveSpaces()
    Dim oCell As Range
    On Error GoTo EndIt
    Application.ScreenUpdating = False
    For Each oCell In ActiveSheet.UsedRange
    If Trim$(oCell.Value) = "" Then oCell.ClearContents
    Next oCell
    EndIt:
    Application.ScreenUpdating = True
    End Sub

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting exact matches and mismatches (2000 SP

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17>It is true! One Hans, makes Excel Work <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17><small>I was going through your code line by line trying to figure this out - and the problem was with the data, not the code!!!

Posting Permissions

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