finding pair of cells with different numbers of words

[victorcarreto]

Hello, I am dealing with texts, not numbers. Column A has a text in English, and column B a text in Portuguese. I need to find pairs of cells A1-B1 or A1234-B1234 whose number of words have a difference of 9 words minimum. For that I have this formula which I wrote on column C, to identify such case by writing the word “MISMATCH”:

=IF(ABS((LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)-(LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1))>=9,"MISMATCH",0)


The thing is that I don´t want to use a column, like I used column C, to add such a formula, because I would be inserting and deleting cells in column A and B, and the formula loses its reference every time a modification like this is done.
So, what I need is a macro that, with just a shortcut key ,finds next or takes me directly to the next pair of cells in the condition of this formula (I mean, a pair A-B in which A or B has more than 9 words in relation to its pair).
Any idea of how it could be done??


Thank you!

 

[martindwilson]

you dont really need a macro us advanced filter put formula in a spare column say d2
=ABS((LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)-(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))>=9
then used advanced filter / filter the list in place/list range a1:b200
/criteria range d1:d2 (note d1 must be blank)
Sheet1

*	A	B	C	D
eng	port	*	*
1 2 3 4 5 6 7 8 9 10 11 12 13	a	*
2 2 3 4 5 6 7 8 9 10 11 12 13	a b	*	*
3 2 3 4 5 6 7 8 9 10 11 12 13	a b c d	*	*
4 2 3 4 5 6 7 8 9 10 11 12 13	*	*	*
5 2 3 4 5 6 7 8 9 10 11 12 13	a b c d e	*	*
a b c d e f g h i j k l m n	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g h i j k l m n o p q	*	*
a b c d e f g h i j k l m n o	a b c d e f g h i j k l m n o p q	*	*
5 2 3 4 5 6 7 8 9 10 11 12 13	a b c d e	*	*
a b c d e f g h i j k l m n	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g h i j k l m n o p q	*	*
a b c d e f g h i j k l m n o	a b c d e f g h i j k l m n o p q	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]1 2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]1 2[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =ABS((LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)-(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))>=9 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


after filter
Sheet1

*	A	B	C	D
eng	port	*	*
1 2 3 4 5 6 7 8 9 10 11 12 13	a	*
2 2 3 4 5 6 7 8 9 10 11 12 13	a b	*	*
3 2 3 4 5 6 7 8 9 10 11 12 13	a b c d	*	*
4 2 3 4 5 6 7 8 9 10 11 12 13	*	*	*
a b c d e f g h i j k l m n	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g	*	*
a b c d e f g h i j k l m n	*	*
a b c d e f g h i j k l m n o p q	a b c d e f g	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:202px;"><col style="width:246px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]1 2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]1 2[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =ABS((LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)-(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))>=9 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4