creating one address label for different people with the same address

[amenu]

I have an Excel spreadsheet sorted alphabetically by last name with several columns among which are (not necessarily in this order): last_name, first_name, address, mailing_label_address. The mailing_label_address will be such that I can use it as an inserted field in a Word mail merge to create mailing labels.

Some of the people listed in the spreadsheet have the same address. As I want to send only one letter to that address, the mailing_label_address will have one of two forms. If the people with the same address have the same last name, e.g., a married couple, the mailing_label_address would look like this:

John and Mary Doe
5 Main St.
Victoria BC

If the people have different last names, the mailing_label_address would look like this:

John Doe
Mary Roe
5 Main St.
Victoria BC

This mailing address would appear in the mailing_label_address column for only one of the two people. It would be blank for the other person.

Is there a way to do this using Excel formulas or would a macro be needed? I do not do VBA programming, so if a macro is needed I would need someone on the forum to supply it.

Thanks for any help with this.
-- Art

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[amenu]

Thanks for the good advice, Orrin. I've attached a workbook with before and after sheets. It can be seen on the before sheet two columns that are empty because I don't know how to code in the information that can be seen in the after sheet. I hope this provides whatever info the Excel gurus on this need to provide the macro or whatever is needed to accomplish the task. --Art

[xladept]

Try this:

Please Login or Register  to view this content.

[amenu]

Hi Orrin,

Thank you for what looks like a lot of work on your part. I installed the macro in my spreadsheet and when I ran it from
the VB window the attached error message appeared. I look forward to hearing from you again!
-- Art

[Arkadi]

amenu,

Any chance you didn't fully copy xladept's code? I tested it on my end using your file and his code, and could not duplicate your error.

[xladept]

Thanks Arkadi!

@ amenu

Or, if you did copy the whole code then maybe the End With has to stand alone so just hit the enter key on the colon so that End Sub is also by itself

[amenu]

Hi Orrin,
I put the End Sub on its own line but am now getting a different error message. I've attached two images. One shows the error message and the other the macro as I have copied and pasted it with the error highlighted in yellow. I hope this provided a clue.
-- Art

[xladept]

Your sheet name must be exact in the code so, if you have a different sheet name replace the "Before" with that name

[amenu]

Yup, I had to replace "Before" with "Sheet2" and rearrange some pre-existing columns that were not in the sample workbook I posted. With that done, it worked a charm. I can't tell you how grateful I am. I am volunteering at a church and some of their office procedures are very inefficient. Excel is not used at all. With the Excel spreadsheet I have created and the macro you created, it will save the office administrator a lot of time. Thank you! --Art

[xladept]

You're welcome and belated thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.