Solved

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

Posted on 2014-02-15
24
603 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
  • 12
  • 5
  • 4
  • +1
24 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
@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
Comment Utility
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
Comment Utility
Yes, I don't get that too, just as @gowflow observes.
0
 

Author Comment

by:judico
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
@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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 80

Accepted Solution

by:
byundt earned 167 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 167 total points
Comment Utility
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 29

Assisted Solution

by:gowflow
gowflow earned 166 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks everybody. Great solutions.
0
 

Author Comment

by:judico
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
@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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now