Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksheet?

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default How do I find duplicate entries in a column in an Excel worksheet?

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksh

Thank you so much JLATHAM, you rock! This helped a lot. FYI, the formula as
written shows "duplicated" if the line above it is a duplicate, so if the
duplicate records are not consecutive it would not work. I changed the "A1"
reference to "A2" so that it would display "duplicated" only if that
particular line was a duplicate record!

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default How do I find duplicate entries in a column in an Excel worksh

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I find duplicate entries in a column in an Excel worksh

In column D enter

=B1 & " " & C1

Double-click the fill handle to increment down D

Use the countif formula in column E by changing cellrefs to column D


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:03:02 -0700, Marsha
wrote:

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default How do I find duplicate entries in a column in an Excel worksh

Thank you so much for your quick response. I will try that and let you know
how it works. Thanks again

"Gord Dibben" wrote:

In column D enter

=B1 & " " & C1

Double-click the fill handle to increment down D

Use the countif formula in column E by changing cellrefs to column D


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:03:02 -0700, Marsha
wrote:

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh

Hello. I am trying to identify duplicates in column A and compared to column
B. The data starts with a text letter, i.e. A1234. How do I search these two
rows to see if there are any duplicates?

"Marsha" wrote:

Thank you so much for your quick response. I will try that and let you know
how it works. Thanks again

"Gord Dibben" wrote:

In column D enter

=B1 & " " & C1

Double-click the fill handle to increment down D

Use the countif formula in column E by changing cellrefs to column D


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:03:02 -0700, Marsha
wrote:

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I find duplicate entries in a column in an Excel worksh

You could use this formula in column C to see if the value in column A of that
row matches any value in column B:

=countif(b:b,a1)0
(if you see true, then you have a duplicate)

And same kind of thing in column D:
=countif(a:a,b1)0
to see if B1 shows up in column A.


Pmatushin wrote:

Hello. I am trying to identify duplicates in column A and compared to column
B. The data starts with a text letter, i.e. A1234. How do I search these two
rows to see if there are any duplicates?

"Marsha" wrote:

Thank you so much for your quick response. I will try that and let you know
how it works. Thanks again

"Gord Dibben" wrote:

In column D enter

=B1 & " " & C1

Double-click the fill handle to increment down D

Use the countif formula in column E by changing cellrefs to column D


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:03:02 -0700, Marsha
wrote:

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.





--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksheet?

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksheet?

I fond this very useful, I needed to create a list of Students with their
parent's name listed once. The raw list had the parent's name listed with
each of their kids, I used the formula to clear the parent's name on their
subsequent children.

Thanks again!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.




  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh

I am looking at two columns to see if there are duplicates. The catch is I
need them to be in the same row. Example.

1 A SA TRUE
1 B DR
2 B DR
1 A SA TRU


I cannot figure out how to write a formula for this.

Any ideas?
Thanks


"Dave Peterson" wrote:

You could use this formula in column C to see if the value in column A of that
row matches any value in column B:

=countif(b:b,a1)0
(if you see true, then you have a duplicate)

And same kind of thing in column D:
=countif(a:a,b1)0
to see if B1 shows up in column A.


Pmatushin wrote:

Hello. I am trying to identify duplicates in column A and compared to column
B. The data starts with a text letter, i.e. A1234. How do I search these two
rows to see if there are any duplicates?

"Marsha" wrote:

Thank you so much for your quick response. I will try that and let you know
how it works. Thanks again

"Gord Dibben" wrote:

In column D enter

=B1 & " " & C1

Double-click the fill handle to increment down D

Use the countif formula in column E by changing cellrefs to column D


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:03:02 -0700, Marsha
wrote:

I want to find duplicates, but they are student names so I need to include 2
columns in the formula. ie first name in column b, last name in colum c.
How can I incorporate two colums? Please reply to
Thanks for your help.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.





--

Dave Peterson

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksh



"WisconsinGreg" wrote:

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksh

Dear WisconsinGreg:

I was able to get the duplicates marked in red (how cool), now how can i
sort these out?

"WisconsinGreg" wrote:

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksh

How cool (all duplicates are marked in red! Is there now a way to sort them
to get rid of them at once?

"WisconsinGreg" wrote:

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How do I find duplicate entries in a column in an Excel worksh

Thanks Greg, this worked well (cool! Dups are in red) Now is there a quick
way to delete these duplicates?

"WisconsinGreg" wrote:

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.




  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh


thanx
it is very usefull.

"JLatham" wrote:

In column C, or another available column, put a formula like this in the
first row with values in A to be examined, then fill the formula on down the
sheet to the end of the list:
=IF(COUNTIF(A$1:A$7,A1)1,"Duplicated","")
Change A$1:A$7 to include your whole range in column a, as perhaps A$2:A$1504

If you can put that in column C, and if there are entries all the way down
the sheet in column B, then you can quickly fill it by moving the cursor to
the lower right corner of the cell with the formula in it until it becomes a
thick + symbol instead of the normal fat cross, then double-click the left
mouse button to fill it all the way to the end.

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh

ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 4).Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(Offsetcount, 4).Value = "dup"
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

Try this I changed the offset count to 4 (four columns to the right) and
entered another line of text (ActiveCell.Offset(Offsetcount, 4).Value =
"dup") which adds the word dub if the value is duplicated.
You can then used the auto filter function to filter / delete duplicated
values.
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh

I have multiple excel files in the same format. I want to combine all the
files into one spreadsheet. I then want to note which ones have duplicate
information (in any of the columns ie. there might be one with same phone #,
one with same address, etc.) Any ideas?

"Riverviewer" wrote:

Thanks Greg, this worked well (cool! Dups are in red) Now is there a quick
way to delete these duplicates?

"WisconsinGreg" wrote:

I'm working with over 5,000 lines, so I wanted a simpler fix.
Found it he http://support.microsoft.com/default.aspx/kb/213355

I've never used a Macro before, but found it was easy to do by following
these steps taken from Excel help:
1. On the Tools menu in Microsoft Excel, point to Macro, and then click
Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. In the module window, press F5. (Before pressing F5 be sure you've
selected the first line of the column you want to check for duplicates--in
Excel.)

(Copy and paste the following Macro into Visual Basic, and press F5.)

Sub FindDups ()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

F5 runs the macro script, and your duplicate lines should be marked in red.
Good Luck!

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I find duplicate entries in a column in an Excel worksh

I know how to find duplicated records, but my problem is that I need to know
where does the value is repeated. Home my ASCII art illustrates what I need.
A B C
1 - a 2x - A4
2 - b 1x - A6
3 - c 1x - 0
4 - a 2x - A1
5 - d 1x - 0
6 - b 2x - A2
7 - e 1x - 0

A show the values
B show how many times the value is repeated
C Shows the cell, or cells where the exactle value is.
Im not sure if its posible to do so, but I had to ask.
Thanks.
  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default How do I find duplicate entries in a column in an Excel worksheet?

Will this same process work for three columns of data, not necessarily
concurrent. I want to ID duplicates where data in A, B and C are all the
same? If so can you help me with the detailed formula to write?


Thanks!

DebDSD

"Chunka" wrote:

I am working with Excel 2000. I have two columns. Column A contains a
Procedure number and column B contains the associated fee. Each procedure
should only be represented once. My goal is to identify instances where a
procedure is repeated (duplicate entries), so that I can determine which
entry is correct. If you are familiar with Access, I am basically trying to
achieve in Excel, the same thing that a "Find Duplicates" query does in
Access.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NO DUPLICATE ENTRIES IN A COLUMN USING EXCEL JOHNNYG Setting up and Configuration of Excel 1 December 10th 05 03:12 PM
How do I find duplicate entries in Excel cher Excel Discussion (Misc queries) 2 June 23rd 05 06:29 PM
How do I set up an Excel column to not allow duplicate entries (m. Staci2s Excel Discussion (Misc queries) 2 June 2nd 05 06:05 PM
How can I find duplicate entries in an entire worksheet? jbrown New Users to Excel 2 May 12th 05 12:41 AM
Find duplicate entries kharrison Excel Discussion (Misc queries) 3 February 25th 05 12:19 AM


All times are GMT +1. The time now is 10:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"