Thread: Formula indicating duplicate values in a range

In exceltip.com i found many solution of my problems

Thanx excel tip...

Regard
SS Patel

Hi. I use excel to hold lists of phone numbers for telemarketing. I'm currently using the semi-manual way of CTRL F to find duplicate numbers.
I've been told that there is a formula you can set up that will detect and highlight duplicate entries within a specified column. I was wondering what is the formula, and how do i set it up?
Thanks
Victoria

I have a list of business names with duplicate business names within the list. I would like to instruct excel to identify those listings which are the same as another without any prompting of what to look for. For example if BHP PTY LTD appears on line A200 and also on A5078 I would like to make those duplicates with true, duplicate or some other refference.

Problem:

Columns A:B contain two lists of values.
How could we create a formula that will check whether each of the lists contains duplicate values (disregarding blanks)?

Solution:

Using the COUNTA and SUMPRODUCT functions as follows:
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7
The formula will return \"\"Duplicates\"\" in case the list includes duplicate values and \"\"No Duplicates\"\" in case such values are not included.


List1____List2
1________1
2
3________B
A________2
________3
1________4

I frequently open sheets sent to me from our field office that have multiple links to sheets located there only.
I must click on the box seeking these sheets several times for each spreadsheet that I open. I would like this turned off.
How do I do it?

I download data from a DB into excel and then convert text to columns.
Some of the well data goes into the proper columns but most don't and usually inserts unwanted columns (in the middle of data) that I have to manually remove.
Since I do this every day, can I control the columns so all the wells make the columns properly?

Hi DHeacock,

Originally Posted by DHeacock

I frequently open sheets sent to me from our field office that have multiple links to sheets located there only.
I must click on the box seeking these sheets several times for each spreadsheet that I open. I would like this turned off.
How do I do it?

You should only get that message once upon opening each workbook, but you say that it is happening multiple times for each one?

You could use code to open them without updating links, but to be honest, I find it hard to believe that it is worth the effort - easier just to press 'N' each time you open a workbook.

HTH, but if not, post back and we'll help with the code option, but I seriously doubt the effort is worthwhile for you,

Alan.

Hi DHeacock,

Originally Posted by DHeacock

I download data from a DB into excel and then convert text to columns.
Some of the well data goes into the proper columns but most don't and usually inserts unwanted columns (in the middle of data) that I have to manually remove.
Since I do this every day, can I control the columns so all the wells make the columns properly?

How are you downloading the data? Are you using MS Query? Also what do you mean by 'wells'?

Thanks,

Alan.

Really exciltip.com is one the best websit in application software this is GOD GIFT FOR ME and I m thankfull my
God ..........................

Hi,

Please help me with the problem.

I get a list of new records everyday in the morning.
that gets updated automatically from the last saved record.

How do i identify the fresh record set

thanks

bharath

Columns A:B contain two lists of values.
How could we create a formula that will check whether each of the lists contains duplicate values (disregarding blanks)?

My Question is...

After determining that there are duplicates, can we make a colorcoding of the items found to be duplicate especially if we are talking of voluminous items?

thank you and best regards.

sincerely,

jC

EXCELTIPS is the best....