• Status: Solved
• Priority: Medium
• Security: Public
• Views: 769

# How to Match a 4X4 Data Matrix in an Excel 2010 Array

The usual search in Excel 2010 attempts to locate one single piece of data, residing in one single cell, in an array of data. I'm trying to find a way to determine whether or not data placed in the cells forming a 4X4 matrix can be found in that same order in an array of data. Do you know of any macro that can do that?
0
judico
• 12
• 5
• 4
• +1
3 Solutions

Commented:
yes sure we can do a macro for you but you need to post a sample workbook so the macro is adapted correctly.
gowflow
0

Author Commented:
Suppose, for instance, data are placed in columns H, I, J and K, as in the attached example.xlsm. We need to check if the block of data

13     5    10     15
16  17       9       5
1      9       8       6
10    7     11       1

exists anywhere in columns H, I, J and K and, if so, place the non-zero contents, starting from cell L1, of the row following that data block, every time an occurrence of the above block is detected.
example.xlsm
0

Commented:
You don't need a macro, it is also possible with formulas.

Search array in A1:D4.
Formula in L1, copied down
=IF(SUMPRODUCT((H1:K1=\$A\$1:\$D\$1)*(H2:K2=\$A\$2:\$D\$2)*(H3:K3=\$A\$3:\$D\$3)*(H4:K4=\$A\$4:\$D\$4)),"Match","x")

See file
example-Array-match.xlsx
0

Commented:
@hgholt

don't know about your formula, but try and change any item in the line that says Match and it should put "X" but it does not ! no matter what you change it does not modify. I tried putting complete wrong info that does not match and still it says Match !!! ???

gowflow
0

Author Commented:
Thanks @hgholt. That's what I needed and I'll award you the points. Nevertheless, I thought it might be more convenient to do that via a macro, so that I can assemble the matching rows after each match, starting at L1, and skip the rest. If you don't want to do that, forget it, I'll award you the points anyway.
0

Author Commented:
Yes, I don't get that too, just as @gowflow observes.
0

Author Commented:
Indeed, as @gowflow has observed, say

13     5   10   15
16   17     9     5
1     1     1     1
10     1     1     1

also gives a match. For some reason, as long the first two rows of \$A\$1:\$D\$4 contain the correct numbers the function ignores what's in \$B\$3:\$D\$4.
0

Author Commented:
This seems to work better -- the individual matches are spelled out explicitly:

=IF(SUMPRODUCT((H1=\$A\$1)*(H2=\$A\$2)*(H3=\$A\$3)*(H4=\$A\$4)*(I1=\$B\$1)*(I2=\$B\$2)*(I3=\$B\$3)*(I4=\$B\$4)*(J1=\$C\$1)*(J2=\$C\$2)*(J3=\$C\$3)*(J4=\$C\$4)*(K1=\$D\$1)*(K2=\$D\$2)*(K3=\$D\$3)*(K4=\$D\$4)),"Match","x")
0

Commented:
ok we can do it 2 ways:

1) Formula, put this in L1 and drag downward.

=IF(SUMPRODUCT((H1=\$A\$1)*(I1=\$B\$1)*(J1=\$C\$1)*(K1=\$D\$1)*(H2=\$A\$2)*(I2=\$B\$2)*(J2=\$C\$2)*(K2=\$D\$2)*(H3=\$A\$3)*(I3=\$B\$3)*(J3=\$C\$3)*(K3=\$D\$3)*(H4=\$A\$4)*(I4=\$B\$4)*(J4=\$C\$4)*(K4=\$D\$4)),"Match " &G1&", "&G2&", "&G3&", "&G4,"No Match")

2) VBA if your interested let me know I will do a macro.

Regards
gowflow
example.xlsm
0

Commented:
Review is always good, did not test that.
The function accepts everything where something fits to give a sumproduct value >0.
Looked fine on first hit, but to match all 4 rows, the sumproduct must equal 4.

This will do it

=IF(SUMPRODUCT((H1:K1=\$A\$1:\$D\$1)*(H2:K2=\$A\$2:\$D\$2)*(H3:K3=\$A\$3:\$D\$3)*(H4:K4=\$A\$4:\$D\$4))=4,"Match","x")

or an array entered formula (Enter with Ctrl+Shift+Enter)

=IF(AND(H1:K1=\$A\$1:\$D\$1,H2:K2=\$A\$2:\$D\$2,H3:K3=\$A\$3:\$D\$3,H4:K4=\$A\$4:\$D\$4),"Match","x")
0

Author Commented:
@hgholt, the first corrected formula works fine but I don't know how to enter the second (=IF(AND(H1:K1=\$A\$1:\$D\$1,H2:K2=\$A\$2:\$D\$2,H3:K3=\$A\$3:\$D\$3,H4:K4=\$A\$4:\$D\$4),"Match","x")) with Ctrl+Shift+Enter on the Windows side of my Macbook Pro. So, I'll stay with the first formula. Will prepare now an example of what I need to do so that you or @gowflow can see if it can be made simpler via VBA.
0

Author Commented:
The goal is to extract and assemble in a separate table the rows following immediately after the matching block For this reason, first the matching is done by referencing the addresses to Sheet1. Then, after the matching in column L is done, a macro is written which first shifts data in L four cells down. The whole G through L column block of data is copied and pasted in Sheet2. Then filtering is carried out and the filtered block is copied onto Sheet3 as the final table. Here's the code of the macro:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("L1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Reference:="Macro1"

Range("G1:L22").Select
Selection.Copy
Sheets("Sheet2").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-4
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("\$G\$1:\$L\$22").AutoFilter Field:=6, Criteria1:="Followup"
Selection.Copy
Range("J33").Select
Sheets("Sheet3").Select
Range("G1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("L2").Select
End Sub

Of course, I'd like to have all of the above, including the filtering, done by a macro. Also, if possible, the whole job maybe is good to be done only in Sheet1 or at most, Sheet2.
example-extract-Array-match.xlsm
0

Commented:
Well I am not sure I got what you want  but try this

The button Find Matches has a macro that will match what you have in col H to K with the block in Col A to E

if it finds the same block it will put in Col M the letter of Col G corresponding and at the end it will copy in sheet2 all the rows that are not matched.

Pls activate macros and activate the button and chk results in sheet2.

Regards
gowflow
example-V01.xlsm
0

Commented:
Here is code that will find the first match for the pattern in a test range. If successful, it selects those cells and displays a message. If not, it displays a different message.

Sub Test()
Dim cel As Range, rgPattern As Range, rgTest As Range
Dim v As Variant
With Worksheets("Sheet1")
Set rgPattern = .Range("A1:D4")
Set rgTest = .Range("H1:K22")
End With
v = MatchPattern(rgPattern.Value, rgTest.Value)
If IsArray(v) Then
Set cel = rgTest.Cells(v(0), v(1))
Application.Goto cel
cel.Resize(rgPattern.Rows.Count, rgPattern.Columns.Count).Select
MsgBox "Top left cell of matching range is: " & cel.Address(False, False)
Else
End If
End Sub

Function MatchPattern(PatternArray As Variant, TestArray As Variant) As Variant
Dim i As Long, ii As Long, j As Long, jj As Long, pBase As Long, pRows As Long, pCols As Long, _
tBase As Long, tRows As Long, tCols As Long
Dim vResults As Variant
Dim bMatched As Boolean
pBase = LBound(PatternArray)
pRows = UBound(PatternArray)
pCols = UBound(PatternArray, 2)
tBase = LBound(TestArray)
tRows = UBound(TestArray)
tCols = UBound(TestArray, 2)
ReDim vResults(tBase To tRows - (pRows - pBase), tBase To tCols - (pCols - pBase))
For i = tBase To tRows - (pRows - pBase)
For j = tBase To tCols - (pCols - pBase)
bMatched = True
For ii = pBase To pRows
For jj = pBase To pCols
If TestArray(i + ii - pBase, j + jj - pBase) <> PatternArray(ii, jj) Then
bMatched = False
Exit For
End If
Next
If bMatched = False Then Exit For
Next
vResults(i, j) = IIf(bMatched, 1, 0)
If bMatched = True Then
MatchPattern = Array(i, j)
Exit Function
End If
Next
Next
End Function
0

Commented:
I've integrated your Macro1 into my previous code:
Sub Test()
Dim cel As Range, rgPattern As Range, rgTest As Range
Dim v As Variant
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set rgPattern = .Range("A1:D4")
Set rgTest = .Range("H1:K22")
End With
v = MatchPattern(rgPattern.Value, rgTest.Value)
rgTest.Offset(0, rgTest.Columns.Count).Resize(UBound(v), UBound(v, 2)).Value = v
Macro1
End Sub

Function MatchPattern(PatternArray As Variant, TestArray As Variant) As Variant
Dim i As Long, ii As Long, j As Long, jj As Long, pBase As Long, pRows As Long, pCols As Long, _
tBase As Long, tRows As Long, tCols As Long
Dim vResults As Variant
Dim bMatched As Boolean
pBase = LBound(PatternArray)
pRows = UBound(PatternArray)
pCols = UBound(PatternArray, 2)
tBase = LBound(TestArray)
tRows = UBound(TestArray)
tCols = UBound(TestArray, 2)
ReDim vResults(tBase To tRows, tBase To tCols - (pCols - pBase))
For i = tBase To tRows
For j = tCols - (pCols - pBase) To tBase Step -1
vResults(i, j) = "x"
Next
Next
For i = tBase To tRows - (pRows - pBase)
For j = tBase To tCols - (pCols - pBase)
bMatched = True
For ii = pBase To pRows
For jj = pBase To pCols
If TestArray(i + ii - pBase, j + jj - pBase) <> PatternArray(ii, jj) Then
bMatched = False
Exit For
End If
Next
If bMatched = False Then Exit For
Next
vResults(i, j) = IIf(bMatched, "Followup", "x")
Next
Next
MatchPattern = vResults
End Function

Private Sub Macro1()
Dim cel As Range
With Sheets("Sheet1")
.Range("L1:L4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Rows(1).Insert
.Range("G2:L23").Copy Sheets("Sheet2").Range("G1")
With .Range("G1:L23")
.AutoFilter
.AutoFilter Field:=6, Criteria1:="Followup"
.Copy Sheets("Sheet3").Range("G1")
.AutoFilter
End With
.Rows(1).Delete
End With
Sheets("Sheet3").Rows(1).Delete
End Sub
example-extract-Array-matchQ2836.xlsm
0

Commented:
Another solution to the same.
Using 2 named ranges MatchRange (Yellow) and SearchRange (green).
Change to actual use.
Also considering if 2 ranges following each other match (blue), and don't has follow up (if that can happen).
All on Sheet1.

Here is the code and a file.

Option Explicit

Sub FindFollowup()
Dim MatchRange() As Variant, SearchRange() As Variant
Dim ws As Worksheet
Dim MatchRow As Long, MatchCol As Integer
Dim SearchRow As Long
Dim MatchUp As Integer, MatchDown As Integer

Set ws = Worksheets("Sheet1")
ws.Select
MatchRange = ws.Range("MatchRange")
SearchRange = ws.Range("SearchRange")
If UBound(SearchRange, 1) < UBound(MatchRange, 1) + 1 Or UBound(SearchRange, 2) < UBound(MatchRange, 2) + 2 Then
MsgBox "Check ranges, Stop"
Exit Sub
End If
If ws.AutoFilterMode = True Then
ws.AutoFilterMode = False
End If

For SearchRow = 2 To UBound(SearchRange, 1)
SearchRange(SearchRow, UBound(SearchRange, 2)) = "x"
If SearchRow > UBound(MatchRange, 1) + 1 Then
MatchUp = 0
For MatchRow = 1 To UBound(MatchRange, 1)
For MatchCol = 1 To UBound(MatchRange, 2)
If SearchRange(SearchRow - UBound(MatchRange, 1) - 1 + MatchRow, MatchCol + 1) = MatchRange(MatchRow, MatchCol) Then
MatchUp = MatchUp + 1
End If
Next MatchCol
Next MatchRow
End If
If SearchRow <= UBound(SearchRange, 1) - UBound(MatchRange, 1) + 1 Then
MatchDown = 0
For MatchRow = 1 To UBound(MatchRange, 1)
For MatchCol = 1 To UBound(MatchRange, 2)
If SearchRange(SearchRow - 1 + MatchRow, MatchCol + 1) = MatchRange(MatchRow, MatchCol) Then
MatchDown = MatchDown + 1
End If
Next MatchCol
Next MatchRow
End If
If MatchUp = UBound(MatchRange, 1) * UBound(MatchRange, 2) And MatchDown < UBound(MatchRange, 1) * UBound(MatchRange, 2) Then
SearchRange(SearchRow, UBound(SearchRange, 2)) = "Followup"
End If
Next SearchRow
ws.Range("SearchRange") = SearchRange
ws.Range("SearchRange").AutoFilter Field:=UBound(SearchRange, 2), Criteria1:="Followup"
End Sub
Array-match.xlsm
0

Commented:
We need to post code as easier to see. Here is my code that you find in the file I already posted and will post a gain for easy convenience.

Sub FindMatches()
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim MaxRow As Long, MaxRow2 As Long, I As Long, K As Long
Dim Foundit As Boolean
Dim sSearch As Range, cCell As Range

Set WS = ActiveSheet
Set WS2 = Sheets("Sheet2")
WS2.Cells.Delete
MaxRow = WS.UsedRange.Rows.Count
MaxRow2 = WS2.UsedRange.Rows.Count
Set sSearch = WS.Range("A1:D4")

For I = 1 To MaxRow

Foundit = True
For Each cCell In sSearch
If cCell.Value <> WS.Cells(I + cCell.Row - 1, cCell.Column + 7) Then
Foundit = False
Exit For
End If
Next cCell

If Foundit Then
WS.Cells(I, "M") = WS.Cells(I, "G")
WS.Cells(I + 1, "M") = WS.Cells(I + 1, "G")
WS.Cells(I + 2, "M") = WS.Cells(I + 2, "G")
WS.Cells(I + 3, "M") = WS.Cells(I + 3, "G")
End If

If WS.Cells(I, "M") = "" Then
WS.Range("G" & I & ":K" & I).Copy WS2.Cells(MaxRow2, 1)
MaxRow2 = MaxRow2 + 1
End If

Next I

MsgBox ("A total of " & MaxRow2 & " rows were copied to sheet2 successfully.")

End Sub

gowflow
example-V01.xlsm
0

Author Commented:
Thank you very much for the great solutions. I don't really know which one to choose, all are great. Now, the problem is how to distribute the points for all of you. I was thinking, maybe each one of you should get 500 points, if experts-exchange.com allows it.
0

Author Commented:
Thanks everybody. Great solutions.
0

Author Commented:
Unfortunately, the only thing the site allowed me was to split the 500 points among the three of you. Also, there was a requirement to choose the best answer, so I had to choose one. Thanks again and have a great day.
0

Commented:
judico,
Please continue to post in this thread as you implement the solution. I think that pattern recognition across an array of cells is an interesting problem, and not one that I have seen asked before.

By discussing implementation issues in this thread, all relevant discussion is captured in one place. Doing so makes the question more valuable in the Experts Exchange database.

0

Author Commented:
@byundt, thanks for the interest. As far as I understand you want me to continue here and not open a new question. I'm concerned, however, that there's no way to award points for the fine solutions that will be provided. Should there be a way to award points even when the question is closed please let me know.

Now, some further development may be the following. I'll be using one of the above solutions which seems simpler to me -- cf. attached example2.xlsm. In that example we have already assembled in Sheet3 the rows immediately following the matches into the \$H\$1:\$K\$9 matrix. This we do by invoking Macro1. It may happen, as in the example at hand, that the extracted matrix will have matching rows. Unlike the first part of the exercise we don't know ahead of time the exact values in the cells of these rows. We now need to analyze via a macro whether there are such matching rows, what exactly they are and how many of each type are there with respect to all the rows in the matrix. In other words, we will now try to start characterizing the newly formed matrix quantitatively. I guess, the method should probably resemble what we already did but I can't figure out how the loops and the logical conditions should be assembled within a macro.
example2.xlsm
0

Commented:
judico,
It was the pattern matching as described in the original question whose discussion I wanted to continue in this thread. If you have new subject matter, such as how to analyze the values returned to Sheet3, that would properly fall into a new question.

The Advanced Filter has the ability to identify the unique records in your Sheet3 cells H1:K9 matrix. It can then copy those unique records to a different set of columns so you can clear the original if desired.

If you want to count how many of each type of duplicate row there are, I suggest that you concatenate the values like this:
=H1 & "|" & I1 & "|" & J1 & "|" & K1

If you copy this formula down, you can then use a COUNTIF formula to count the number of duplicate rows of each type. Alternatively, you can use a PivotTable on the concatenated values, and thereby build the report automatically. Note that you will need to add header labels to the data before building the PivotTable.

0

Author Commented:
I was thinking that quantitative characterization of the obtained in Sheet3 table may help to simplify the initial problem if it is expanded. Let's for simplicity observe a 1X4 matrix instead of the 4X4 matrix in the original problem and let's have 5 numbers (1 through 5) which this 1X4 matrix is made of. Suppose now we need to find the followup matching rows in the original array for every combination and permutation of the 1X4 vector members. Each one of the combinations and one of its permutation will generate a Sheet3 matrix of varying size and properties. It appears that, as a first step, instead of having all the Sheet3 tables, one easy way to put together all the outcomes is to represent them as numbers, reflecting the repeating rows vs. all rows of each individual table.

I understand that analysis of the Sheet3 table itself is a separate problem and I'll open a new question for it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.