Locating similar numbers in a column

BJS123

New Member
Joined
Feb 16, 2007
Messages
24
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Carvel_P

Board Regular
Joined
Feb 15, 2007
Messages
88
Hi there,

You could try, in cell B11:

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

Kevin
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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

New Member
Joined
Feb 16, 2007
Messages
24

ADVERTISEMENT

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. :oops:

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Feb 16, 2007
Messages
24

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Feb 16, 2007
Messages
24
Got it. :LOL:

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

Thanks a lot jonmo

BJS
 

Watch MrExcel Video

Forum statistics

Threads
1,122,486
Messages
5,596,448
Members
414,066
Latest member
rjoiner3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top