Mark matched strings in one column

[mouaffaq]

Hi profs.
I have this second challenge>>>>>

i have the data in the image below,
i want to mark the string in any cell in column C if its matched or partially matched of any other cell in the same column, using the below condition:


cell $A of the same cell of the string we are searching = Cell $A of the "matched or partially matched cell"

AND

cell $B value of the same cell of the string we are searching = exactly Cell $B value of the "matched or partially matched cell"

If both not matched it should not be marked, other wise we should mark it.

means:

1-if C1 = any of $C:$C130000 and,
A1 = exactly the same record value, and B1= exactly the same record value then mark the row by putting 1 beside the record (D column)

2- if C1 exists in the start of string we are matching and A1 = the same record value, and B1= the same record value, if found then mark the row by putting 1 beside the record (D column)

We should run the macro or VBA starting C1 .... end of file, to check all cells in this column.

Am using excel 2007

Thanks A lot

 

[Hui]

Can you try this in either D2 or E2
=IF(AND(A2=B2,MATCH(C2,C3:$C$130000,0)>0),1,0)
Copy down

 

[mouaffaq]

Can you try this in either D2 or E2
=IF(AND(A2=B2,MATCH(C2,C3:$C$130000,0)>0),1,0)
Copy down

thanks for the reply

i don't want to check if A = B,

I want to check ie.
C1 with all cells in Column C

for example suppose (C1 = C132 or C1 is the first part of C132 string)
now I must chk (if A1 = A132 & B1=B132), if true then put 1 in D1
else loop starting C2

I hope is clear now

 

[bosco_yip]

Maybe......

In D1, copy down :

=IF(A1="","",IFERROR(--(MATCH(A1&B1&C1&"*",INDEX(A2:A$10&B2:B$10&C2:C$10,0),0)>0),0))

Regards

 

[mouaffaq]

Maybe......

In D1, copy down :

=IF(A1="","",IFERROR(--(MATCH(A1&B1&C1&"*",INDEX(A2:A$10&B2:B$10&C2:C$10,0),0)>0),0))

Regards

Why it is A2:A$10
is it only matching 10 records or each one to end of cells which is approx. 132000?

 

[mouaffaq]

also bosco its taking for ever to finish

 

[mouaffaq]

Nope Bosco
Not correct

 

[mouaffaq]

Can you try this in either D2 or E2
=IF(AND(A2=B2,MATCH(C2,C3:$C$130000,0)>0),1,0)
Copy down

thanks for the reply

i don't want to check if A = B,

I want to check ie.
C1 with all cells in Column C

for example suppose (C1 = C132 or C1 is the first part of C132 string)
now I must chk (if A1 = A132 & B1=B132), if true then put 1 in D1
else check C1 with C133 ...

at end loop starting C2

I hope is clear now

 

[Hui]

D1:
=IF(MATCH(C1&"*",C2:$C$130000,0)>0,IF(AND(A1=INDEX(A2:$A$130000,MATCH(C1&"*",C2:$C$130000,0),0),B1=INDEX(B2:$B$130000,MATCH(C1&"*",C2:$C$130000,0),0)),1,0),0)
Copy down

see attached

 

[Haseeb A]

Hello,

May be...

=IFERROR((MMULT((INDEX(A2:B$130000,MATCH(C1&"*",C2:C$130000,0),)=A1:B1)+0,{1;1})=2)+0,"")

 

[mouaffaq]

Can you try this in either D2 or E2
=IF(AND(A2=B2,MATCH(C2,C3:$C$130000,0)>0),1,0)
Copy down

D1:
=IF(MATCH(C1&"*",C2:$C$130000,0)>0,IF(AND(A1=INDEX(A2:$A$130000,MATCH(C1&"*",C2:$C$130000,0),0),B1=INDEX(B2:$B$130000,MATCH(C1&"*",C2:$C$130000,0),0)),1,0),0)
Copy down

see attached

thanks a lot
seems is working
but some data appears N/A
Could you please check so the results becomes 100% correct
thanks

 

[Hui]

Can we have some data?

 

[mouaffaq]

Can we have some data?

Can we have some data?

please check full data below:

www.almutadaber.com/1.xlsx

Thanks a lot mate, this means a lot to me

 

[mouaffaq]

Did u find the problem
Is it sovlable?
Thanks

 

[Hui]

Apart from putting an Iferror() around the function

=Iferror(=IF(MATCH(C1&"*",C2:$C$130000,0)>0,IF(AND(A1=INDEX(A2:$A$130000,MATCH(C1&"*",C2:$C$130000,0),0),B1=INDEX(B2:$B$130000,MATCH(C1&"*",C2:$C$130000,0),0)),1,0),0),0)

I can't think of a better way