checking two range of data base on an specific cells value with VBA or formula

[hamidrezaxy]

Hi;

In attached I have 4 columns, and I want a VBA or formula to check and compare data in A and compare with all related in C ,based on B column's value which contains "A" then finally result should be in D column.
For example if all cells in A which contains "abcd0002" and have "A" in B column, consider as refrence, then check with all related in C.
clear example is attached.

So many thanks in advance. cheking.xlsx

[AB33]

This is my understanding of your request.
If Column B contains the word "A", copy reference in to column D, but not when you say, compare will all related in C. Where do you all the other values in column D? Yes, No?

[hamidrezaxy]

Let me explain it more:

example;

if in first row :
A is "abcd0002" ll B is "A" ll C is "Hold" ll D should be write "refrence" .......[ and should be considered as a refrence for other rows which have "abcd0002" in A column , any value in B column then check it with C column for comparing-> result in D (Yes/No) ]

in other word all uniq value in A column should have same status in C column. "A" shows refrence for comparing.

This is killing me, plz help me.

This is my understanding of your request.
If Column B contains the word "A", copy reference in to column D, but not when you say, compare will all related in C. Where do you all the other values in column D? Yes, No?

[Ace_XL]

In D2..array entered

=IF(B2="A","Reference",IF(INDEX(D$2:D2,MATCH(1,(A$2:A2=A2)*(C$2:C2=C2),0))="Reference","Yes","No"))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy down