I have a list of 15, 595 name, address, city etc. In many 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!

I have a list of 15, 595 name, address, city etc. In many 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.discussion

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 one mailing label per address, unless I remove their names and just use "Residents". I think that is my only way out of this, I just hate to lose the more personal touch.
Thanks!

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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!

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 he
https://skydrive.live.com/#cid=9378A...121822A3%21191
for the workbook "Joannie" and right-click and download it.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

CLAUS YOU ARE A GENIUS.

I have no idea how your mind works to solve problems like that! 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.

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2