Solved

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

Posted on 2014-02-15
24
652 Views
Last Modified: 2014-02-17
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
Comment
Question by:judico
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 4
  • +1
24 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39861406
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 Comment

by:judico
ID: 39861748
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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39862016
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")

Open in new window


See file
example-Array-match.xlsx
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 30

Expert Comment

by:gowflow
ID: 39862044
@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 Comment

by:judico
ID: 39862049
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 Comment

by:judico
ID: 39862061
Yes, I don't get that too, just as @gowflow observes.
0
 

Author Comment

by:judico
ID: 39862077
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 Comment

by:judico
ID: 39862116
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
 
LVL 30

Expert Comment

by:gowflow
ID: 39862402
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")

Open in new window


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

Regards
gowflow
example.xlsm
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39862609
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")

Open in new window


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")

Open in new window

0
 

Author Comment

by:judico
ID: 39862714
@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 Comment

by:judico
ID: 39862743
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

Open in new window


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
 
LVL 30

Expert Comment

by:gowflow
ID: 39862942
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
 
LVL 81

Accepted Solution

by:
byundt earned 167 total points
ID: 39862952
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
    MsgBox "Pattern not found in test range"
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
MatchPattern = "Not found"
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

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39863010
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
MatchPattern = "Not found"
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

Open in new window

example-extract-Array-matchQ2836.xlsm
0
 
LVL 22

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 167 total points
ID: 39863026
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

Open in new window

Array-match.xlsm
0
 
LVL 30

Assisted Solution

by:gowflow
gowflow earned 166 total points
ID: 39863052
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

Open in new window


gowflow
example-V01.xlsm
0
 

Author Comment

by:judico
ID: 39863089
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 Closing Comment

by:judico
ID: 39863131
Thanks everybody. Great solutions.
0
 

Author Comment

by:judico
ID: 39863168
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
 
LVL 81

Expert Comment

by:byundt
ID: 39863293
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.

Brad
0
 

Author Comment

by:judico
ID: 39866520
@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
 
LVL 81

Expert Comment

by:byundt
ID: 39866532
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.

Brad
0
 

Author Comment

by:judico
ID: 39866592
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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question