If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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 doubleclick 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




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 doubleclick 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




How do I find duplicate entries in a column in an Excel worksh
In column D enter
=B1 & " " & C1 Doubleclick 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 doubleclick 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




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 > > Doubleclick 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 doubleclick 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




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 > > > > Doubleclick 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 doubleclick 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




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 > > > > > > Doubleclick 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 doubleclick 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




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 duplicatesin 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




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. > > 
Thread Tools  
Display Modes  


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 