Count matches between two columns based on complex criteria

[Hamond]

Hi,

I have data in a column B and C consisting of zero and ones which I want to match up but there is some complex criteria and stages

Stage 1) First I would like to identify each group of 1s in column B. A group is defined whenever there is a zero either before or after the occurrence of a 1, i.e. the groups are broken out by zeros

For example in the attached worksheet there are 35 instances of the number one but only 7 groups of 1s occur.

In a situation where the first number in the series starts with a 1 e.g. in cell b4 then the first group would start from b1 to the last occuring consecutive 1 before the first zero occurs .

Stage 2) Second, once I have the start position of each group in column B, I then need to find a match for each of the groups in column C. But a match only occurs if in column C there is a 1 either at the 1st or 2nd occuring 1 in each group in column B.

So for example, in the spreadsheet the first group in Column B is cells B6:B10 (these all contains 1s), in column C, a 1 does not occur in either C6 or C7, therefore this would not constitute a match!


I already worked out a way to find instances where there is a match for the 1st occuing 1 in column C for each group (see column J - returns one match) using the formula below but I need to extend this to work for the 2nd occurances as well.

=IF(AND(OR(B3=0,ISTEXT(B3)),B4=1,C4=1),1,"")


So based on this new criteria, the first true match would occur in group 3 (B47:B49). In this case, cell C48 has a 1 in it, so I would like to count this has a match. The only other match is in group 6 (B158:B165), where C158 has 1 in it.

I have manually calculated the matches by entering “Yes” in column E.

I’m looking for either a formula or vba solution where it gives me either a count of the number of groups matched based on the criteria or populates another column such as column E indicating a match for a given group.


Ideally it would be great if the number of periods to compare in a match (in this case 2) can be made dynamic. So if I change the period search from 2 to 3, then a match in column C would still occur for an given group if the 1st, 2nd or 3rd occuring 1 in each group has a corresponding match (1) in column C.


Sorry for the long post. Hope it makes sense and someone can help!


Thanks,


Hamond

[mdmackillop]

Here's a start for you.
[VBA]Sub Macro1()
Dim tgt As Range
Set tgt = Range("O1")
Columns("B:C").Copy tgt
For Each cel In tgt.Offset(2).CurrentRegion
If cel = 0 Then cel.Clear
Next
Range("O3").Clear

Set tgt = tgt.End(xlDown)
Do
If Application.Sum(tgt.Offset(, 1).Resize(2)) > 0 Then
tgt.Offset(, -10) = "Match"
Else
tgt.Offset(, -10) = "xxx"
End If

If tgt(2) = 1 Then
Set tgt = tgt.End(xlDown).End(xlDown)
Else
Set tgt = tgt.End(xlDown)
End If
If tgt.Row = Rows.Count Then Exit Do
Loop
End Sub
[/VBA]

[shrivallabha]

I am little confused with the result for the first group you have stated.

Try this formula in cell J4 and copy down

=IF(C4<>1,"",IF(C3=0,IF(OR(B4=1,B5=1),"Yes",""),""))

It shows "Yes" J47 instead of J48 as detailed in your manual column. Am I missing something?

[Hamond]

Thanks MD and Shrivallabha.

Shrivallabha, I tried your fomula but it returns nothing even in J47. In terms of the confusion, for me it doesn't matter whether the match flag is popoluated in j47 or j48 as long the match is correctly flagged to the right group. Because ultimately I will use countif function to get the total matches which is the high level number I am interested.

Md - your code works great but I wasn't expecting it to run so slowly! I will need to loop through lots of different data so a faster formula solution might be more suited! But I will see if I can play around with the code to make it run faster, that's once of course I understand how it works!

Hamond

[shrivallabha]

Thanks MD and Shrivallabha.

Shrivallabha, I tried your fomula but it returns nothing even in J47. In terms of the confusion, for me it doesn't matter whether the match flag is popoluated in j47 or j48 as long the match is correctly flagged to the right group. Because ultimately I will use countif function to get the total matches which is the high level number I am interested.

Md - your code works great but I wasn't expecting it to run so slowly! I will need to loop through lots of different data so a faster formula solution might be more suited! But I will see if I can play around with the code to make it run faster, that's once of course I understand how it works!

Hamond

My apologies. The formula needs to be reversed like:

=IF(B4<>1,"",IF(B3=0,IF(OR(C4=1,C5=1),"Yes",""),""))

Refer attached file's Column F which has heading By Formula.

[mdmackillop]

This uses a filter which is quicker
[VBA]
Sub Macro1()
Dim tgt As Range
Dim r As Range
Set tgt = Range("O1")
Columns("B:C").Copy tgt

Set r = Range(Cells(3, 15), Cells(3, 15).End(xlDown))
r.AutoFilter Field:=1, Criteria1:="0"
r.SpecialCells(xlCellTypeVisible).Clear

Set tgt = tgt.End(xlDown)
Do
If Application.Sum(tgt.Offset(, 1).Resize(2)) > 0 Then
tgt.Offset(, -10) = "Match"
Else
tgt.Offset(, -10) = "xxx"
End If

If tgt(2) = 1 Then
Set tgt = tgt.End(xlDown).End(xlDown)
Else
Set tgt = tgt.End(xlDown)
End If
If tgt.Row = Rows.Count Then Exit Do
Loop
End Sub

[/VBA]

[Hamond]

Thanks MD & Shrivallabha.

MD - now the code works much faster. Shrivallabha, your formula also works great.

Thanks,

Hamond