VLOOKUP to find repeats?

[wilster2012]

Below is a small sample of data that I am working with. Test parts are identified as 01-24 in column A. Location in Column B is location of test part. The tech in Column C will be S1, S2 and S3. Column D is the max value (resistance) for each Tech. Once a part is used in one test, it cannot be used again. In the sample below, I want t keep the first part 04 and part 17 and discard the second 04 and 17. Will VLOOKUP work for that? There are a least 200 data point to deal with, so I want to automate the process. I am new to VLOOKUP, o any help would be appreciated.


[TABLE="width: 254"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Part
[/TD]
[TD="width: 64, bgcolor: transparent"]Loc[/TD]
[TD="width: 64, bgcolor: transparent"]Tech[/TD]
[TD="width: 82, bgcolor: transparent"]Max of Value[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04[/TD]
[TD="bgcolor: transparent"]04-TRA[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]216[/TD]
[TD="bgcolor: transparent"]Keep[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05[/TD]
[TD="bgcolor: transparent"]05-TRA[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]215[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]06[/TD]
[TD="bgcolor: transparent"]06-TRA
[/TD]
[TD="bgcolor: transparent"]S2
[/TD]
[TD="bgcolor: transparent, align: right"]214
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]10-BLB[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]211
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]04[/TD]
[TD="bgcolor: transparent"]04-BLB[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]211[/TD]
[TD="bgcolor: transparent"]Discard[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]08
[/TD]
[TD="bgcolor: transparent"]08-TRA[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]208[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]22[/TD]
[TD="bgcolor: transparent"]22-TRA[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]207
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]03[/TD]
[TD="bgcolor: transparent"]03-BLB[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]207[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]17[/TD]
[TD="bgcolor: transparent"]17-TRA[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]206[/TD]
[TD="bgcolor: transparent"]Keep[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]17[/TD]
[TD="bgcolor: transparent"]17-BLB[/TD]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent, align: right"]205
[/TD]
[TD="bgcolor: transparent"]Discard
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance
Willy

 

[Marcelo Branco]

Try

E2 copied down
=IF(COUNTIF(A:A,A2)>1,IF(COUNTIF(A$2:A2,A2)=1,"Keep","Discard"),"")

M.

 

[wilster2012]

That works! Thanks!