Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a way
to do this?

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a way
to do this?

OK, I can see that one, but what if I need it to tell me the information that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a way
to do this?

If you copy down the formula then apply a filter you can filter on any
number greater than 1

--


Regards,


Peo Sjoblom

"Mandy" wrote in message
...
OK, I can see that one, but what if I need it to tell me the information
that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through
both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a
way
to do this?

ok and how do i apply a filter?

"Peo Sjoblom" wrote:

If you copy down the formula then apply a filter you can filter on any
number greater than 1

--


Regards,


Peo Sjoblom

"Mandy" wrote in message
...
OK, I can see that one, but what if I need it to tell me the information
that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through
both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a
way
to do this?

http://www.contextures.com/xlautofilter01.html

--


Regards,


Peo Sjoblom

"Mandy" wrote in message
...
ok and how do i apply a filter?

"Peo Sjoblom" wrote:

If you copy down the formula then apply a filter you can filter on any
number greater than 1

--


Regards,


Peo Sjoblom

"Mandy" wrote in message
...
OK, I can see that one, but what if I need it to tell me the
information
that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through
both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there
a
way
to do this?

Hi,

To apply a filter
1. Select your data and choose Data, Filter, AutoFilter
2. From the drop down over the formula column choose Custom,
3. From the first drop down in the dialog box pick Greater than or equal to
4. In the next box enter 1.
5. Click OK



"Mandy" wrote:

ok and how do i apply a filter?

"Peo Sjoblom" wrote:

If you copy down the formula then apply a filter you can filter on any
number greater than 1

--


Regards,


Peo Sjoblom

"Mandy" wrote in message
...
OK, I can see that one, but what if I need it to tell me the information
that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through
both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a
way
to do this?

Hi,

Although I posted how to do this with a filtered list, you might consider
conditional formatting.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=COUNTIF($A$1:$B$4,A1)1
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=COUNTIF($A$1:$B$4,A1)1
5. Click the Format button and choose a format.
6. Click OK twice

Note that this formula check for duplicated in any location that means if 1
is repeated twice in column A it will mark it as a duplicate. The formula
filter approach we discussed in the previous posts only check for duplicates
between columns, not within columns although one could do that also with a
formula approach.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a way
to do this?

Hey Try This
First sort the data then put this function
IF(A2=A1,"dup","ok")

if its help

click "yes"

Regards

Hardeep kanwar

"Mandy" wrote:

OK, I can see that one, but what if I need it to tell me the information that
is duplicated. So if I use phone numbers for instance

123-555-1212 987-555-1212
987-555-1212 123-555-1212

Can I have a cell that shows me the numbers that are duplicated through both
columns?

"porter444" wrote:

You could use a simple COUNTIF function in columns C & D like this:
=COUNTIF(A:A,A2)


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"Mandy" wrote:

Can some one tell me how to look up duplicates.

If I have 2 columns:

A B
1 2
2 3
3 5
4 1


If I have this and I want to find duplicates in each column is there a way
to do this?