Combining Duplicates

[JoannieMaj]

I have a list of 15, 595 name, address, city etc. In many cases, th
husband/name/address/etc. is on the line above the wif
name/address/etc. I want to create one mailing label for each coupl
and I have had no success filtering as they are all "unique" record
(with a different "name"). Please help! Thanks

 

[JoannieMaj]

Not sure if I was clear. The spreadsheet is set up like this:

Smith Jean 4 Main Street Anytown, USA 89445
Smith John 4 Main Street Anytown, USA 89455
Jones Bob 8 Main Street Anytown, USA 89445
Brown Sally 15 Main Street Anytown, USA 89445
Brown Robert 15 Main Street Anytown, USA 89445



I don't see how filtering on "address" would help me to achieve on
mailing label per address, unless I remove their names and just us
"Residents". I think that is my only way out of this, I just hate t
lose the more personal touch.
Thanks!

;1611675']> I have a list of 15, 595 name, address, city etc. In man
cases, -

the husband/name/address/etc. is on the line above the wife
name/address/etc. I want to create one mailing label for each couple
and I have had no success filtering as they are all "unique" records
(with a different "name"). Please help! Thanks!-

Why not filter on 'address'?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[Claus Busch]

Hi Joannie,

Am Thu, 9 May 2013 21:02:45 +0100 schrieb JoannieMaj:

Not sure if I was clear. The spreadsheet is set up like this:

Smith Jean 4 Main Street Anytown, USA 89445
Smith John 4 Main Street Anytown, USA 89455
Jones Bob 8 Main Street Anytown, USA 89445
Brown Sally 15 Main Street Anytown, USA 89445
Brown Robert 15 Main Street Anytown, USA 89445

try:

Sub Test()
Dim LRow As Long
Dim i As Long

Application.ScreenUpdating = False
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
If Cells(i, 3) & Cells(i, 4) = _
Cells(i - 1, 3) & Cells(i - 1, 4) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & _
" / " & Cells(i, 2)
Rows(i).Delete
End If
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi Joannie,

Am Thu, 9 May 2013 23:17:21 +0200 schrieb Claus Busch:

For i = LRow To 2 Step -1
If Cells(i, 3) & Cells(i, 4) = _
Cells(i - 1, 3) & Cells(i - 1, 4) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & _
" / " & Cells(i, 2)
Rows(i).Delete
End If
Next

in your example you have some spaces in your strings. Then you better
try:

Sub Test()
Dim LRow As Long
Dim i As Long

Application.ScreenUpdating = False
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
With WorksheetFunction
If .Trim(Cells(i, 3) & Cells(i, 4)) = _
.Trim(Cells(i - 1, 3) & Cells(i - 1, 4)) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & _
" / " & Cells(i, 2)
Rows(i).Delete
End If
End With
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch

 

[JoannieMaj]

Well Claus, that made my head explode! It is WAY above my understandin
or level of competence!
The info is actually in columns (Last Name, First Name, Address
City/State/Zip). I could use concatenate to make a text string out o
them but the rest of that scares the beejeebus out of me. Maybe I'l
duplicate the file and experiment with what you suggested. Nothing t
lose, right?
THANK YOU - all who have responded - you are the best

 

[Claus Busch]

Hi Joannie,

Am Fri, 10 May 2013 19:55:49 +0100 schrieb JoannieMaj:

Well Claus, that made my head explode! It is WAY above my understanding
or level of competence!
The info is actually in columns (Last Name, First Name, Address,
City/State/Zip). I could use concatenate to make a text string out of
them but the rest of that scares the beejeebus out of me. Maybe I'll
duplicate the file and experiment with what you suggested. Nothing to
lose, right?
THANK YOU - all who have responded - you are the best!

Make a copy of your workbook, sort the table by City and Address, Press
Alt + F11 => Insert => module. Copy and paste the code to that module
and run the macro.
In your example the rows
Brown Sally 15 Main Street Anytown, USA 89445
Brown Robert 15 Main Street Anytown, USA 89445
will be concatenate to:
Brown Robert / Sally 15 Main Street Anytown, USA 89445
The rows:
Smith Jean 4 Main Street Anytown, USA 89445
Smith John 4 Main Street Anytown, USA 89455
will stay as is because the ZIP differs.
You can look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Joannie" and right-click and download it.


Regards
Claus Busch

 

[JoannieMaj]

CLAUS YOU ARE A GENIUS.

I have no idea how your mind works to solve problems like that! I
worked like a charm and my 15K name mailing list is now less than 8K!
That represents a tremendous savings in postage, labels, the whole nin
yards.

I cannot thank or recommend you enough - you are a superstar!
Joanni

 

[Claus Busch]

Hi Joannie,

Am Sat, 11 May 2013 16:53:17 +0100 schrieb JoannieMaj:

It worked like a charm and my 15K name mailing list is now less than 8K!
That represents a tremendous savings in postage, labels, the whole nine
yards.

always glad to help. Thank you for the feedback.
Have a nice weekend.


Regards
Claus Busch