Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-13-2013, 05:37 PM
jgross30 jgross30 is offline Consolidate names Windows 8 Consolidate names Office 2013
Novice
Consolidate names
 
Join Date: May 2013
Posts: 4
jgross30 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-13-2013, 06:15 PM
macropod's Avatar
macropod macropod is offline Consolidate names Windows 7 64bit Consolidate names Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 05-13-2013, 06:29 PM
jgross30 jgross30 is offline Consolidate names Windows 8 Consolidate names Office 2013
Novice
Consolidate names
 
Join Date: May 2013
Posts: 4
jgross30 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-13-2013, 07:10 PM
macropod's Avatar
macropod macropod is offline Consolidate names Windows 7 64bit Consolidate names Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 05-13-2013, 07:16 PM
jgross30 jgross30 is offline Consolidate names Windows 8 Consolidate names Office 2013
Novice
Consolidate names
 
Join Date: May 2013
Posts: 4
jgross30 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-13-2013, 07:25 PM
macropod's Avatar
macropod macropod is offline Consolidate names Windows 7 64bit Consolidate names Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 05-13-2013, 08:04 PM
jgross30 jgross30 is offline Consolidate names Windows 8 Consolidate names Office 2013
Novice
Consolidate names
 
Join Date: May 2013
Posts: 4
jgross30 is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 05-13-2013, 08:36 PM
macropod's Avatar
macropod macropod is offline Consolidate names Windows 7 64bit Consolidate names Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
consolidate, duplicates, list

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidate names 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft