Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to locate duplicate data
I have a spreadsheet of about 14,000 lines. Four Columns (A) First Name, (B)
Last Name, (C) Login and (D) Email Address. I need to find out if any of these contacts have the same email address. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to locate duplicate data
Take a look on Advanced Filter in Help menu
"Robin Rescue" wrote: I have a spreadsheet of about 14,000 lines. Four Columns (A) First Name, (B) Last Name, (C) Login and (D) Email Address. I need to find out if any of these contacts have the same email address. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to locate duplicate data
You can use conditional formatting to highlight any duplicates.
I'll assume the email addresses are in the range D1:D14000 Select the range D1:D14000 Goto FormatConditional Formatting Formula Is: =COUNTIF(D$1:D$14000,D1)0 Click the Format button Select the Pattern tab Set the fill color to light yellow (?) OK out Biff "Robin Rescue" <Robin wrote in message ... I have a spreadsheet of about 14,000 lines. Four Columns (A) First Name, (B) Last Name, (C) Login and (D) Email Address. I need to find out if any of these contacts have the same email address. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to locate duplicate data
Hi Robin Rescue
I can recommend that you copy all the columns to a single excle sheet. This is basicall to run an advance query which will fetch you all the information for you to compare. I shall explain you how to do it. Assuming that you have copied all the columns to a single spread sheet, you know the column heading now, save the excel sheet. Now I wiil be using the Advance filtering techniques so that all the column information are retrieved even if the typed cells are misspelled, which normally is not the case when you do a simple filtering technique. The filtered values may not be correct. To retieve the values; 1. Select all records and the column headings and define a name for the range List. This is the List range name. 2. Select all column headings and paste in a seperate row anywhere in the spreadsheet. 2. Select the pasted column heading along with an empty row and define a name. This is the criteria range name. 3. Define a criteria on the second row for all columns, if necessary. For example S* will display all details specific to "S'. 4. Click Data- Filter- Advanced Filter 5. Type the List name 6. Type the Criteria Name 7. Click copy to another location option - Mandatory 8. Click Copy to 9. Click the cell below the criteria range All data will be displayed specific to the query you requested. This is very usefull if the spreadsheet data are mistyped or mis-spelled. Now you have done a database funtion on your excle spread sheet. Challa Prabhu "Robin Rescue" wrote: I have a spreadsheet of about 14,000 lines. Four Columns (A) First Name, (B) Last Name, (C) Login and (D) Email Address. I need to find out if any of these contacts have the same email address. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |