#1
|
|||
|
|||
Consolidate names
I have a very large contact list. column A list last names. column b list first names. columns c-f contain personal information on that names from the list. i want to filter out duplicates that have more information in c-f. so if there is a duplicate name from last name ( Column A), first name (column B) just keep the one with the extended information.
Last edited by jgross30; 05-13-2013 at 05:39 PM. Reason: wrong form |
#2
|
||||
|
||||
What happens if one record has some fields in columns c-f completed and another has different fields filled in, but neither has a complete set (some may even differ for the same field)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
that is correct. for instance gross josh might appear multiple times and each time have different columns filled in for each record. for the most part there are only duplciates nothing more for a record. some records do not have any data just a duplicate. i want to consolidate all this data so each name has one record and all extended data.
|
#4
|
||||
|
||||
You could try a macro like:
Code:
Sub DeleteDuplicates() Application.ScreenUpdating = False Dim LRow As Long, i As Long, j As Long, k As Long, bDel As Boolean With ActiveSheet LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row For i = LRow To 2 Step -1 For j = i - 1 To 1 Step -1 If .Cells(i, 1).Value = .Cells(j, 1).Value Then If .Cells(i, 2).Value = .Cells(j, 2).Value Then For k = 3 To 6 If .Cells(i, k).Value <> "" Then bDel = True Exit For End If Next If bDel = True Then .Rows(j).EntireRow.Delete Else .Rows(i).EntireRow.Delete End If End If End If Next Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Where would I put my data ??
Last edited by macropod; 05-13-2013 at 07:23 PM. Reason: Deleted unnecessary quote of entire post replied to |
#6
|
||||
|
||||
According to your first post, you already have your data - in which case, you don't have to put it anywhere. All you need to do is to install & run the macro.
For installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm. Although these are for Word, the principles for Excel are the same.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
i ran the macro. i do not follow the function. can i email you the file. my email is «email address removed for privacy». there is a list of names than start over after a few. i have attached the file here
Last edited by macropod; 05-13-2013 at 08:37 PM. Reason: email address removed for privacy |
#8
|
||||
|
||||
Updated workbook attached. I had to make a few minor changes to the macro because your file has content other than just the data rows. I also added a progress report that appears on the status bar. To run the macro press Alt-F8 select 'DeleteDuplicates', then OK.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
consolidate, duplicates, list |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
matching names in column A with names in column C | bob999999 | Excel | 1 | 04-28-2013 11:21 AM |
Consolidate - does not work in 2007 version | Jirik61 | Excel Programming | 10 | 08-08-2012 04:59 AM |
names on bars | Psychopig7 | Project | 1 | 06-22-2012 04:58 AM |
How to enter names in Resource Pool/names | pstein | Project | 1 | 03-26-2012 07:37 AM |
Names | dguillory | Word | 0 | 12-29-2009 11:00 PM |