• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Finding If There are Matching Rows in a Matrix

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
judico
Asked:
judico
  • 7
  • 3
  • 2
1 Solution
 
gowflowCommented:
Isn't this the same as a previous question ?
gowflow
0
 
judicoAuthor Commented:
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
 
gowflowCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
judicoAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
judicoAuthor Commented:
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
 
judicoAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
judicoAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
judicoAuthor Commented:
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
 
judicoAuthor Commented:
Thank you very much for this very sophisticated solution of the problem.
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now