Hamond
03-03-2012, 08:52 AM
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
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