Array formula?

[Smegg]

Hi

I am trying to carry out a simple matching conditional type formula.
<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=240 border=0><COLGROUP><COL style="WIDTH: 60pt" span=3 width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 60pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=80 height=20>1</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 60pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=80>U</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 60pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=80>F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">U</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">U</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">U</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">U</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">D</TD></TR></TBODY></TABLE>
For all the each reference number in column A I would like to make sure at least one of those rows column B matches column C. So for example 'reference 1' does not have any rows where column B matches column C where as 'reference 3' does match on one occurrence. For each row this would display in the last column whether that reference matches or not in at least one of the occurrences.

I assume it would be some sort of array formula, but cannot quite figure it out

Thanks!

 

[Yard]

In Column D, maybe:

=SUMPRODUCT(--(A$1:A$10=A1),--(B$1:B$10=C$1:C$10))>0

 

[Smegg]

THANKS YOU!!!! I knew it was a simple solution

 

[Smegg]

Ooh, what does '--' actually mean

Thanks again, its great here

 

[Yard]

See

http://www.mrexcel.com/forum/showthread.php?t=75721