Coutif and offset with something else

[bboysen]

Hi all,

Here is my problem today.

I have a list of joint info that i need to find if they are duplicated joints or are the same joint just cut up.

I have the formula for finding the Duplicated Joints just based on the joint info. But now I need to find the ones that are really a different joint and not the same joint cut up.

Here is what I have been working on
=IF(COUNTIF(E:E,E10)>1,IF(COUNTIF(F:F,OFFSET(E10,0,1))>1,"","DUP"),"")

What I need it to do is look at the E column and find the Duplicated Joint then if it shows it as a duplicated joint look at the F column and see if it has the same Heat Number as the first one.
It is close or it could be way off.

Here is also a part of my workbook to help you understand what i am talking about.
So Row 7 and 8 are Duplicated Joint and Rows 9 and 10 are also duplicated joints. But if you look at rows 14 and 15 they are not. They come from the same joint and you can tell by the length of the joint.

Excel 2010

	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: center"]Pipe Length[/TD]
[TD="align: center"](Degrees)[/TD]
[TD="align: center"]Joint Number[/TD]
[TD="align: center"]Heat Number[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"][/TD]
[TD="align: center"]022-1[/TD]
[TD="align: center"]F41880[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"] 55.1 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-1 [/TD]
[TD="align: center"] F41553 [/TD]

[TD="align: center"] 8 [/TD]
[TD="align: center"] 76.1 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-1 [/TD]
[TD="align: center"] R4232 6[/TD]

[TD="align: center"] 9 [/TD]
[TD="align: center"] 78.6 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-2 [/TD]
[TD="align: center"] R42326 [/TD]

[TD="align: center"] 10 [/TD]
[TD="align: center"] 78.3 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-2 [/TD]
[TD="align: center"] F41553 [/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]78.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1000-3[/TD]
[TD="align: center"]F41553[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1000-4[/TD]
[TD="align: center"]F41553[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]78.4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1000-6[/TD]
[TD="align: center"]R42245[/TD]

[TD="align: center"] 14 [/TD]
[TD="align: center"] 46.2 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-8 [/TD]
[TD="align: center"] R42326 [/TD]

[TD="align: center"] 15 [/TD]
[TD="align: center"] 53.8 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] 1000-8 [/TD]
[TD="align: center"] R42326 [/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]77.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1001-1[/TD]
[TD="align: center"]R42245[/TD]

</tbody>

Master Tally Spread 1​

Thanks for your help and hope you all have a good day!

 

[sheetspread]

Using the above C5:F16 as the sample range paste this in G6 and copy down: =IF(AND(COUNTIF($E$6:$E$16,E6)>1,SUMPRODUCT(--($E$6:$E$16=E6),--($F$6:$F$16=F6))<2),"DUP","")