| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate entries in more than one column
hellor,
Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Duplicate entries in more than one column
I'd use a helper column and concatenate the first and last names with a unique
string: =a1&"..."&b1 and drag down Then look for duplicates in this column. ZoR wrote: hellor, Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Duplicate entries in more than one column
Hello Dave,
Thanks for your help. Concatenating the data is a good idea but my records are more than 5000! Also, is there anyway to copy a formula to a range of cells without having to drag the formula all the way DOWN to all the records? Concatenating works for small number of records. With a large number, i don't think it will be easy to scroll up and down to see which ones are duplicated or not! "Dave Peterson" wrote: I'd use a helper column and concatenate the first and last names with a unique string: =a1&"..."&b1 and drag down Then look for duplicates in this column. ZoR wrote: hellor, Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Duplicate entries in more than one column
You can copy a formula down by double-clicking on the fill handlel(small black
square at bottom right corner of cell) Will copy down as far as you have data in an adjacent column. What do you want to do with the duplicates? You can get a list of all the Uniques by DataFilterAdvanced FilterUnique records only and copy to another location. See Chip Pearson's site for more methods of finding duplicates. http://www.cpearson.com/excel/Duplicates.aspx Gord Dibben MS Excel MVP On Wed, 30 Jan 2008 13:54:01 -0800, ZoR wrote: Hello Dave, Thanks for your help. Concatenating the data is a good idea but my records are more than 5000! Also, is there anyway to copy a formula to a range of cells without having to drag the formula all the way DOWN to all the records? Concatenating works for small number of records. With a large number, i don't think it will be easy to scroll up and down to see which ones are duplicated or not! "Dave Peterson" wrote: I'd use a helper column and concatenate the first and last names with a unique string: =a1&"..."&b1 and drag down Then look for duplicates in this column. ZoR wrote: hellor, Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Duplicate entries in more than one column
hello Gord,
Thanks for helping out with the copy paste thing. For duplicates, I have more than 5000 records with peoples first and last name in two seperate columns. i want to identify those whose names have been duplicate or have multiple entries. i would prefer highlighting all duplicate entries as well as their DUPLICATES with respect to their FULL NAME i.e. grabbing data from both columns and then comparing it with all remaning rows. please help me out there! thanks. "Gord Dibben" wrote: You can copy a formula down by double-clicking on the fill handlel(small black square at bottom right corner of cell) Will copy down as far as you have data in an adjacent column. What do you want to do with the duplicates? You can get a list of all the Uniques by DataFilterAdvanced FilterUnique records only and copy to another location. See Chip Pearson's site for more methods of finding duplicates. http://www.cpearson.com/excel/Duplicates.aspx Gord Dibben MS Excel MVP On Wed, 30 Jan 2008 13:54:01 -0800, ZoR wrote: Hello Dave, Thanks for your help. Concatenating the data is a good idea but my records are more than 5000! Also, is there anyway to copy a formula to a range of cells without having to drag the formula all the way DOWN to all the records? Concatenating works for small number of records. With a large number, i don't think it will be easy to scroll up and down to see which ones are duplicated or not! "Dave Peterson" wrote: I'd use a helper column and concatenate the first and last names with a unique string: =a1&"..."&b1 and drag down Then look for duplicates in this column. ZoR wrote: hellor, Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Duplicate entries in more than one column
I thought you were going to use Dave's formula =a1&"..."&b1 to give you a third
column to filter or flag per Chip's methods. After doing that, copy that column and Paste SpecialValuesOKEsc. Chip's site is a good place to start for flagging duplicates. Gord On Thu, 31 Jan 2008 06:34:00 -0800, ZoR wrote: hello Gord, Thanks for helping out with the copy paste thing. For duplicates, I have more than 5000 records with peoples first and last name in two seperate columns. i want to identify those whose names have been duplicate or have multiple entries. i would prefer highlighting all duplicate entries as well as their DUPLICATES with respect to their FULL NAME i.e. grabbing data from both columns and then comparing it with all remaning rows. please help me out there! thanks. "Gord Dibben" wrote: You can copy a formula down by double-clicking on the fill handlel(small black square at bottom right corner of cell) Will copy down as far as you have data in an adjacent column. What do you want to do with the duplicates? You can get a list of all the Uniques by DataFilterAdvanced FilterUnique records only and copy to another location. See Chip Pearson's site for more methods of finding duplicates. http://www.cpearson.com/excel/Duplicates.aspx Gord Dibben MS Excel MVP On Wed, 30 Jan 2008 13:54:01 -0800, ZoR wrote: Hello Dave, Thanks for your help. Concatenating the data is a good idea but my records are more than 5000! Also, is there anyway to copy a formula to a range of cells without having to drag the formula all the way DOWN to all the records? Concatenating works for small number of records. With a large number, i don't think it will be easy to scroll up and down to see which ones are duplicated or not! "Dave Peterson" wrote: I'd use a helper column and concatenate the first and last names with a unique string: =a1&"..."&b1 and drag down Then look for duplicates in this column. ZoR wrote: hellor, Well, i hope someone here will be able to help me! My data is as follows: LAST NAME FIRST NAME 1. ABC OPP 2. XYZ MGF 3. RTY SED 4. QRS WER 5. ABC OPP 6. RTY SED 7. QFG CVT 8. POL MUH I want to identify the duplicate entries by grabbing the data from both columns that is Last Name and First Name as well. So that I get rows 1 &5 as well as rows 3 &4 highlighted. All the help information that you have given in the community is for data in ONe COLUMN ONLY. You will be of great help if you could add a solution to my problem as well! Looking forward to your reply -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count duplicate entries in column | Excel Discussion (Misc queries) | |||
| NO DUPLICATE ENTRIES IN A COLUMN USING EXCEL | Setting up and Configuration of Excel | |||
| How to remove duplicate entries in column? | Excel Discussion (Misc queries) | |||
| How do I set up an Excel column to not allow duplicate entries (m. | Excel Discussion (Misc queries) | |||
| Preventing Duplicate Entries within a column | Excel Discussion (Misc queries) | |||
Linear Mode
