Thread: Matching very large number of cells in array

1. 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

2. Hi Liz

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. 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. 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

5. 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?

6. Originally Posted by Maudibe
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.

7. ...just thought I might have missed something. There is always a method to your madness

8. 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. 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
•