Solved

Finding If There are Matching Rows in a Matrix

Posted on 2014-02-17
12
199 Views
Last Modified: 2014-02-18
An array of data placed in four columns may contain matching rows. For instance, invoking Macro1 in the attached example2.xlsm leads to a table in Sheet3 with 3 matching rows of one kind and 2 matching rows of another kind. I'd like first to have a macro recognize that there are matching rows at all and then show which these exact rows are and what is their ratio with respect to the overall number of rows. I tried using the advanced filter but the unique rows always included one of the matching rows. Thanks in advance.
example2.xlsm
0
Comment
Question by:judico
  • 7
  • 3
  • 2
12 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39866919
Isn't this the same as a previous question ?
gowflow
0
 

Author Comment

by:judico
ID: 39867679
Yes, indeed, it's quite similar but in the previous question we knew beforehand the exact values in the cells of the matrix. Here we need to test the matrix for matching rows and count these rows. I tried the testing for matching rows with the advanced filter but the 'unique records only' includes also one member of each matching row. The counting of matching rows isn't clear to me as well.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39867818
Hold on !!!!

Pls specify what need to be matched with what.

Here is my understanding:
1) Your matrix is in M1 to P4
is that correct ?

2) the lookup columns are G to K and specifically H to K to find the matrix
is that correct ?

If your answer is yes for both then I don't see what is different now ? if the answer is no then please clarify by specifics Column reference with row reference.

gowflow
0
 

Author Comment

by:judico
ID: 39867864
The matrix to be analyzed is H2:K10 in Sheet3 (I've added top row H1:K1 because of the filter requirement). The macro needs to determine that rows 3, 5 and 6 as well as rows 8 and 10 are matching (remember, that matrix is a result of what was done in the previous question and it may or may not contain matching rows). When I try to do that with the advanced filter the result (using 'unique records only') also includes a sample of the matching rows which defeats the purpose of extracting only non-matching rows. Further, how do you count the number of matching rows?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39868056
In your previous question the goal was to find rows following immediately the 4x4 matrix match.
That are the rows marked "Followup".

It is not clear what you mean saying rows 3, 5 and 6 match.
The 3 rows have the numbers 9, 11, 8, 7, so they match each other in the output, but nothing in the 4x4 matrix..

What I understand from above is that these rows must only be marked once.
So if a row with the numbers 9, 11, 8, 7 are found earlier, then it should not be marked "Followup".
Is that correct?

Here is my previously send file with a change, so any followup row only appear in the list one time.

Counting of the rows using Countif, see L1 in the file.
Array-match-2.xlsm
0
 

Author Comment

by:judico
ID: 39868617
This one is a new problem. It only concerns the matrix in Sheet3 (in my example2.xlsm above), not the original matrix in Sheet1 (neither in my old example.xlsm, nor in my current example2.xlsm). The macro needed in this question should determine something different from what was already discussed. It's goal is to find out if there are rows in the Sheet3 matrix (in my example2.xlsm, attached above), which repeat themselves. If the macro finds out that there indeed are repetitious rows in the matrix in Sheet 3 then it should count how many times these rows repeat themselves and what is the proportion of each type of repetitious rows with respect to all rows in Sheet3.

Now, notice that in the new file, example2.xlsm, I have expanded the original matrix in Sheet1 so that it can provide an example in Sheet3, having, among all found 'followup' rows, two types of 'followup' rows that have repetitious rows -- row 9      11      8      7, which repeats three times and row 14      3      6      11, which repeats two times. It's for the macro, however, to find that there are repetitious rows in the matrix of Sheet3, to count them individually, and to state what percent of all matrix rows each set of repetitious rows is.

In your Array-match-2.xlsm you're analyzing the earlier initial matrix in which there were no repetitious 'followup' rows.
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

 

Author Comment

by:judico
ID: 39868672
Also, when using =COUNTIF([Array],"[String]") one has to know ahead of time the value [String]. In this case, however, the counted repetitious rows 9      11      8      7 and 14      3      6      11 are not known in advance. The macro has to first detect them and then somehow place into the COUNTIF function. Also, the macro has to determine the exact [Array] where the search is to occur.
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39868859
I inserted the new dataset from example2, with the repetitious rows, and analysed on that.
Checked for previous "Followup", so any row only was 1 time in result.
Obviously that is not what you need.

Here is a new file with a count column for number of times each Followup occur.
All Followup shown in result.
Count and percentage of the number of Followup is shown for the first of each.
Result copied to Sheet2.
Array-match-3.xlsm
0
 

Author Comment

by:judico
ID: 39868914
Thanks @hgholt. This is very close to what I need. Is it possible to show separately, in a different place of Sheet3 (Sheet2 in your example) the exact repeating patterns and the percentage. For instance,

9      11      8      7       33.3%
14      3      6      11     22.2%
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39868950
Do you mean copy column B to E and H, without header, where Count >1 to Sheet3?
Only values without colour background?

I look at it tomorrow, it is late now here.
0
 

Author Comment

by:judico
ID: 39869366
I guess, this final filtering accomplishes the goal -- cf. your Array-match-3a.xlsm with that final fix. This is a very sophisticated solution of the problem and I'm awaiting your permission to upload it to the initial question, to have all the fine solutions in one place. I'm awarding you with great pleasure the 500 points. Thanks again very much.
Array-match-3a.xlsm
0
 

Author Closing Comment

by:judico
ID: 39869368
Thank you very much for this very sophisticated solution of the problem.
0

Featured Post

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

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

15 Experts available now in Live!

Get 1:1 Help Now