Locating similar numbers in a column

BJS123

New Member
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".

TIA

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi there,

You could try, in cell B11:

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

Kevin

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

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.

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

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.

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

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.

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

Replies
2
Views
326
Replies
4
Views
350
Replies
9
Views
235
Replies
3
Views
434
Replies
1
Views
61

1,217,679
Messages
6,137,947
Members
450,100
Latest member
mscetr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back