Locating similar numbers in a column

[BJS123]

Hi all.

How do I locate 2 similar numbers in a column?

Example:

Cells a1 to a10 contains numbers 1-10 and cell a11 contains the number 5.

I want to locate the number 5, by putting a formula in the adjacent column so that cell b11 announces "redundant". Is it a Lookup or what?

To put it in a query in Column B: If number x (cell) is redundant in column A, write "redundant".

Any helpfull suggestions?

TIA

 

[Carvel_P]

Hi there,

You could try, in cell B11:

=IF(ISERROR(VLOOKUP(A11,A1:A10,1,FALSE)),"","Redundant")

Kevin

 

[Jonmo1]

=IF(COUNTIF(A1:A10,A11),"redundant","")

 

[Dan Waterloo]

It looks like you have two related questions:
1. Is the value in A11 found anywhere in the list A1:a10? If so, then note that in B11.
2. If there is such a redundant value (or values) then identify it in cells B1 to B10.

Dealing with question 2 first, write this formula in B1
=IF(A1 = A$11, "Redundant", "")
and copy it down to B10.

Then put this ARRAY formula in B11 to deal with question 1:
=IF(OR(A11=A1:A10),"Redundant","")

You may need to read up on array formulas if you haven't used them befire.

 

[BJS123]

OK, didn´t explain this one very well. I´ll try to be more specific.

First of all, forgot to tell it was Office03. Think the 07 has this function per default.

In column A is a series of numbers, mixed. If a number is repeated more than one time I want the neighbouring cell in column B to write "redundant".

This is to ensure that I can isolate the redundant numbers, and end up with unique numbers in column A

Hope this explanation was better.

BJS

 

[Jonmo1]

in B1

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

Fill Down.

This will display in (column B) "redundant" for all duplicates in column A - EXCEPT the FIRST occurance of each item.

 

[BJS123]

Hi jonmo

tried =IF(COUNTIF(A$1:A1,A1)>1,"redundant","") but it returns #name?

a few questions:
Why a$1 Should it not be a2, a3, a4, and so on when you fill down?

what does A1,A1 do?

Hope you can help me out

BJS

 

[Jonmo1]

in B1, formula will have

A$1:A1

B2 = A$1:A2
B3 = A$1:A3
B4 = A$1:A4 etc...

the A$1:A1 is there so each formula as it's filled down, will only be looking in it's own row or ABOVE - not below. This way the First occurance of a duplicate will not be flagged. Only 2nd,3rd,4th etc.. will get marked "redundant"

I have no idea why you're getting NAME...
it works fine for me.

double check spelling.

 

[BJS123]

Got it.

Error reason: Working on a DK version, missed a . + I need to for some reason to replace , with a ; (???)

Thanks a lot jonmo

BJS