Selecting unique value from inside a dynamic range

[LtCmdrData]

Hello all
i have a bit of a poser here, i have a set of values spread over 3 colomns and a unique reference number in a forth.
i need to identify where there are duplications of the values and mark accordingly.
i am using the equation =if(countif(b:d,b2)>1,"x","") what i would like to be able to do is instead of using the x to mark the record, actually identify where the duplication is happening, for this i would use the lowest unique number which is stored as an alpha numeric combination in colomn a. is what i am trying to do at all possible or am i just living in the land of the fairies?


My ideal result

URN reg par_reg ult_reg dupe check
abc01 1 a aa abc01
abc02 2 b bb abc02
abc03 3 c cc
abc04 4 d d abc04
abc05 5 e d abc04
abc06 6 f 6 abc06
abc07 7 a ee abc01
abc08 8 1 1 abc01
abc09 9
abc10 1 abc01
abc11 2 abc02

where there is a blank is where no duplication has been found

thanking you all in advance for any help you may be able to offer

 

[SkipVought]

Hi,

You could use the PivotTable Wizard to aggregate the COUNT of...

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[LtCmdrData]

i am not sure i follow you on this. is this something that will then be able to be used as an output field to populate the cells with? no guesses as to who uses pivot tables never at all

 

[AnotherHiggins]

So you want to find any duplicates that occur in columns B, C and/or D, right? So this line:
[tt]
URN reg par_reg ult_reg dupe check
abc04 4 d d abc04[/tt]

Is considered a dupe because the same value appears in C & D.

What if you add the following line to your data set:
[tt]
URN reg par_reg ult_reg dupe check
abc04 1 Q Q[/tt]

It contains a dupe in that C&D both contain Q, but column B is also a dupe because there are other 1s. So what would be returned for that?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[LtCmdrData]

the return value should be the lowest possible value in column a .... column a is a set of unique so there will not be any dupes within it.. from the example the return would be abc01 to go in the final column