Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Matching very large number of cells in array

    Dear Loungers,

    I have a spreadsheet that is approximately 200 rows by 250 columns. i need to do something like index & match to find and row(s) that are duplicate patterns. However the thought of a string of 250 concatenated values - is that even possible anyway? - is too horrific to consider. In the attached sheet I need to find all reating patterning in the 250 country columns.

    I think I should have a vb function that allows me to repeat this since I am bound to want to do this again. The logic would be something like this:

    For each Row
    ....find a match in any other row
    ....Report the Row number(s) [in separate cells in a perfect world]

    I thought the MS fuzzy match add-in might help but it didn't.

    Any help?

    Thank you........................... liz
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Liz

    I've looked at your file.
    Still not clear what you are asking for.
    For example, do you want to check if the contents in cell [B3] appear anywhere else in column [B]???
    And do you want to know if contents in cell [C4] appear anywhere else in column [C]. etc etc for all rows.

    zeddy

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    very frustrating my reply just got lost!
    So what i need to do is match the whole of the array in E4:IT4 to find any other "row" in the detail area from E4: IT1012 (I said 250 in my original post, sorry was misremembering). A small example would be like this:

    Row 1: EXC EXC NEX NFS.... findpattern result = "Row 1, Row 3"
    Row 2: EXC EXC EXC NFS.... findpattern result = ""
    Row 3: EXC EXC NEX NFS.... findpattern result = "Row 1, Row 3"
    Row 4: EXC EXC NEX EXC.... findpattern result = "Row 4, Row 6, Row 7"
    Row 5: EXC EXC NFS NFS.... findpattern result = ""
    Row 6: EXC EXC NEX EXC.... findpattern result = "Row 4, Row 6, Row 7"
    Row 7: EXC EXC NEX EXC.... findpattern result = "Row 4, Row 6, Row 7"

    Does this help?

    Thank you........................ liz

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Liz,

    I think this will do what you want. I currently have the output going to the Immediate window in the VBE you can change the Debug.print statement and route it to a file or even to a cell in the row being checked.
    Code:
    Option Explicit
    
    Sub FindDups()
    
       Dim lCurRow As Long
       Dim lCurCol As Long
       Dim lFirstCol As Long
       Dim lFirstRow As Long
       Dim lLastCol  As Long
       Dim lRowCnt   As Long
       Dim lCompareRow As Long
       Dim bMatch    As Boolean
       Dim zMatches  As String
       
       lFirstRow = 3
       lFirstCol = 5  'Set number = letter of first column to match E=5, AA = 27
       lLastCol = 9   'Set number = letter of last column to match
       lRowCnt = [A2].End(xlDown).Row()
       
       For lCurRow = lFirstRow To lRowCnt
          
          zMatches = ""
          
          For lCompareRow = lFirstRow To lRowCnt
          
             bMatch = True
         
             If lCurRow = lCompareRow Then
               ' Do nothing don't check against self
             Else
          
               For lCurCol = lFirstCol To lLastCol
                  If Cells(lCurRow, lCurCol).Value <> _
                     Cells(lCompareRow, lCurCol).Value Then
                    bMatch = False
                    Exit For
                  End If
               Next lCurCol
               
               If bMatch Then zMatches = zMatches & "Row " & Format(lCompareRow, "0000") & " "
                 
             End If   'lCurRow = lFirstRow
             
          Next lCompareRow
          
          If zMatches <> "" Then Debug.Print "Row: " & Format(lCurRow, "0000") & " Matches " & zMatches
          
       Next lCurRow
       
       
    End Sub
    Sample Output:
    Code:
    Row: 0008 Matches Row 0007 
    Row: 0022 Matches Row 0023 Row 0025 
    Row: 0023 Matches Row 0022 Row 0025 
    Row: 0025 Matches Row 0022 Row 0023
    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Color Matches

    A slight variation to RG's code will color code matching rows using the cells in Column A.

    @RG: Nicely done! Any reason why you started the match with column 5 instead of column 2?
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by Maudibe View Post
    Any reason why you started the match with column 5 instead of column 2?
    Maud,

    Per the OP E4....
    array in E4:IT4
    Of course the sample file didn't have all the cols. That's also why I made it easy to change Starting and Ending Cols with the initialization of the respective variables.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    ...just thought I might have missed something. There is always a method to your madness

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG/Maudibe

    Nice code.
    When I have to refer to particular column numbers in VBA, I prefer to use something like
    [E1].Column rather than 5, or
    [AA1].Column rather than 27
    ..as it is reminds me directly of the column, and I don't have to keep counting on my fingers.
    So [APK1].column is a lot easier than whatever that column number is.

    Now, although you have provided a solution to the question posed, I can't help wondering what the purpose of this would be.
    For example, if it was to allow subsequent removal of these 'duplicate' entries, then this can be achieved directly:

    In Excel 2007 or later..

    To remove "duplicate" records..
    Put cellpointer in any heading cell, then..
    In top-panel Ribbon, select..
    Data>Remove Duplicates
    ..then uncheck any columns you want to ignore for duplicates
    For example, you can start checking from column [E] onwards etc etc.as per original request

    Or, If you wish to highlight any duplicate entries with Conditional Formatting:
    select entire sheet (Ctrl-A), or specified range of cells, then..
    In top-panel Ribbon, select..
    Home>Conditional Formatting>Highlight Cell Rules>Duplicate Values

    Perhaps Excel 2007, 2010, 2013 Users are unaware of these excellent built-in features
    Thought I'd just mention them in this context.

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-03-01)

  10. #9
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Retired Geek,

    Was pulled off onto an emergency and just getting back to this,... looks good I will have a play. Thank you once again for being "retired"!!!

    liz

Posting Permissions

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