Get rid of #Value! error

[Gary Drumm]

Column 1 is a list of about 300 equipment asset numbers, formatted as text.
I would like to check for duplicates. (Sample numbers might be 10H754, or 10G520)

In column 2 I used Andrew P's. formula =if(countif(A$1:A2,A2)=1,Max(B$1:B1)+1,"")).

In column 3 he uses =if(Max(B:B)<Row(1:1),"",Index(A:A,MAtch(Row(1:1),B:B,False))).


I copied those down, and both columns B and C have #Value! errors top to bottom.

It sort of works, I can change one of my asset numbers to be exactly like another, and it will read false, instead of Value.

I don't know how to get it cleaned up, to eliminate the #Value! errors.
A blank space would be better, easier to look at or scan.

What might work better?
I haven't worked in Excel for a while, and I'm very rusty.
Thanks,
Gary

 

[mahoney403]

I believe there is a value error because you have a mixture of text and numbers but I may be incorrect.

As for the duplicates, you could try sorting column 1, then conditionally format the column to highlight duplicate values in red.

 

[CodeNinja]

Try:

=IF(COUNTIF($A$1:A1,A1)>1,"Duplicate","")

Copy the formula down the entire range. It will put "Duplicate" where there is more than 1 item with the same string but will ignore the first instance.

 

[Gary Drumm]

Thanks,
I tried both.
The Conditional Format solution looks promising in that both the original and duplicate are highlighted.
Upon adding a third duplicate in the mix, it too was highlighted, and that's good.
However, in my test it shows the original to be a Duplicate, and it may be doing it in decending order.
Any thoughts on that? Would it see the first occassion of that number as the original?

I have other unique (numeric) part numbers associated with each of these (alpha) asset numbers.
I suppose I'll have to include those to weed out the differences when found.

Thanks,
Gary

 

[mahoney403]

The one problem with Conditional Formatting duplicates is that it will always highlight the original and the duplicates that follow, so its easies if sorted.

 

[Gary Drumm]

The formula version also showed the first occassion of the number in descending order, to be the original, whether it was or not. Hope that makes sense.
The 5th number down originally was 10G758; when I changed it to 10G071, and it's column to the right stayed blank.
The 21st number down was my original 10G071, and it immediately showed up as a duplicate, when I changed line 5.
My 10th number down originally was 10G750, and I changed it to 10G071, and it immediately showed duplicate.

I'll keep playing with it.
Thanks again