Home |
Search |
Today's Posts |
#1
|
|||
|
|||
concatenate problem
I have a database of names, addresses, and miscellaneous data that I'm trying to de-dup. The problem is that duplicate names and addresses do not have duplicate values in the other columns. What I need to do is combine the values for the other columns into one entry for each unique name and address.
What I'm trying to do is identical to using the subtotal tool, if only the subtotal tool allowed me to use the concatenate function, rather than, say, sum or average. Any thoughts? |
#2
|
|||
|
|||
Posting an example of your problem would probably garner you several
efficient means for accomplishing your task "joe peters" wrote: I have a database of names, addresses, and miscellaneous data that I'm trying to de-dup. The problem is that duplicate names and addresses do not have duplicate values in the other columns. What I need to do is combine the values for the other columns into one entry for each unique name and address. What I'm trying to do is identical to using the subtotal tool, if only the subtotal tool allowed me to use the concatenate function, rather than, say, sum or average. Any thoughts? -- joe peters |
#3
|
|||
|
|||
I combined three different mail lists into one list, and need to remove the duplicates. Unfortunately, each of the three lists has valuable info that needs to be kept that the other two don't, so I can't simply eliminate the duplicate names. I need to compress the values from each entry into one entry.
Example: 5 columns: "Salutation", "First", "Last", "Address", "E-mail" 3 rows: each has the same First and Last name. One row has the e-mail, one has the salutation, and the third has the address. How do I compress the three entries into one? There are some names that only occured on one or two lists rather than all three that got combined, so there's no uniform number of entries per person. This is why I'm thinking about the subtotal tool from the Data menu - if I could run a concatenate subtotal for each change in the name column, it would do the trick exactly. |
#4
|
|||
|
|||
I would make a unique list of all first and last names using Data
Filter -Advanced filter --Unique in a separate part of your w/sheet Then do Vlookup for individual categories to go right next to the list that you created above. "joe peters" wrote in message ... I combined three different mail lists into one list, and need to remove the duplicates. Unfortunately, each of the three lists has valuable info that needs to be kept that the other two don't, so I can't simply eliminate the duplicate names. I need to compress the values from each entry into one entry. Example: 5 columns: "Salutation", "First", "Last", "Address", "E-mail" 3 rows: each has the same First and Last name. One row has the e-mail, one has the salutation, and the third has the address. How do I compress the three entries into one? There are some names that only occured on one or two lists rather than all three that got combined, so there's no uniform number of entries per person. This is why I'm thinking about the subtotal tool from the Data menu - if I could run a concatenate subtotal for each change in the name column, it would do the trick exactly. -- joe peters |
#6
|
|||
|
|||
Try
="hello "&" tello"&" bello" or =A1&B1 |
#7
|
|||
|
|||
That did a great job of de-duping the list for me, but it only returned the values from a single entry per name. For example, I have Bob Smith listed twice, once with an e-mail address and once with a mailing address. The VLookup apparently only looked until it found the first entry for Bob Smith, and only returned the e-mail address.
How do I make the VLookup compile data from multiple rows? [quote=N Harkawat]I would make a unique list of all first and last names using Data Filter -Advanced filter --Unique in a separate part of your w/sheet Then do Vlookup for individual categories to go right next to the list that you created above. |
#8
|
|||
|
|||
I have 9,900 entries, with around 1,800 duplicate names. I really have no desire to go through them on an individual basis.
Quote:
|
#9
|
|||
|
|||
from reading your other post, it sounds like you have one column of names
(which are duplicated), but the data adjacent to the name may not be duplicated and you want to move the data adjacent to the name before deleting it. I thought you just wanted to delete the duplicate items - obviously you will not want to use my previous suggestion (not right away, anyway). Would your data look like the following? Name Address Email phone Jim Moore xxx Nancy Smith yyy Jim Moore zzz Jim Moore aaa Since Jim is duplicated, you'd want to move his data up to the first line - then delete the duplicates. "JMB" wrote: You could copy the following macro into a module in VBA, select the row/column you want and run the macro. Be advised, it will delete the entire row or column that contains the duplicate data, so if you have data above/below your table you'll want to move it to a blank sheet, remove dupes, then move it back. Also, cannot undo, always a good idea to have a backup. Sub DeleteDuplicates() Dim Collection1 As New Collection Dim Range1 As Range Dim Direction As Byte On Error Resume Next With Selection If .Rows.Count 1 And _ .Columns.Count = 1 Then Direction = 1 ElseIf .Columns.Count 1 And _ .Rows.Count = 1 Then Direction = 2 Else: Exit Sub End If End With For Each x In Selection Collection1.Add x.Value, CStr(x.Value) If Err < 0 Then Err.Clear If Range1 Is Nothing Then Set Range1 = x Else: Set Range1 = Union(Range1, x) End If End If Next x Select Case Direction Case 1: Range1.EntireRow.Delete Case 2: Range1.EntireColumn.Delete Case Else: Exit Sub End Select End Sub "joe peters" wrote: I have 9,900 entries, with around 1,800 duplicate names. I really have no desire to go through them on an individual basis. Ola Wrote: Try ="hello "&" tello"&" bello" or =A1&B1 -- joe peters |
#10
|
|||
|
|||
You could copy the following macro into a module in VBA, select the
row/column you want and run the macro. Be advised, it will delete the entire row or column that contains the duplicate data, so if you have data above/below your table you'll want to move it to a blank sheet, remove dupes, then move it back. Also, cannot undo, always a good idea to have a backup. Sub DeleteDuplicates() Dim Collection1 As New Collection Dim Range1 As Range Dim Direction As Byte On Error Resume Next With Selection If .Rows.Count 1 And _ .Columns.Count = 1 Then Direction = 1 ElseIf .Columns.Count 1 And _ .Rows.Count = 1 Then Direction = 2 Else: Exit Sub End If End With For Each x In Selection Collection1.Add x.Value, CStr(x.Value) If Err < 0 Then Err.Clear If Range1 Is Nothing Then Set Range1 = x Else: Set Range1 = Union(Range1, x) End If End If Next x Select Case Direction Case 1: Range1.EntireRow.Delete Case 2: Range1.EntireColumn.Delete Case Else: Exit Sub End Select End Sub "joe peters" wrote: I have 9,900 entries, with around 1,800 duplicate names. I really have no desire to go through them on an individual basis. Ola Wrote: Try ="hello "&" tello"&" bello" or =A1&B1 -- joe peters |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
label problem | Excel Worksheet Functions |