Question:
Address labels using excel and transpose?
K-9
2008-01-20 14:11:29 UTC
I am producing an address list suitable for merging to labels.

My address list is huge and have thousands to do. The problem is they are in column A, 5-7 rows long with 2 rows separating them. I have tried using "Special Paste" and using transpose, but that seems to only work if I select the rows I want. If I paste more than one address at a time, it ends up going across the page filling lots and lots of columns. I dont want to using Word to merge just yet, as I need to sort this data first. Can anyone offer any advice, i'm not an excel expert, but I can follow instructions ok! I've tried the various websites, but they dont seem to offer instruction on more than a couple of rows of data.
thanks
Seven answers:
2008-01-21 10:29:34 UTC
You've got a lot of work to do because the list hasnt been set up properly. Each part of the name and address record should be in separate columns. So firstly, I would insert another row as row 1 by right clicking on row 1 address and down to insert. Now on the new Row 1 enter the column names of Title, First Name, Surname, Address 1, Address2, Address3, County, Postcode etc. etc.

Now click in the cell that contains your first entry. You will cut and paste bit by bit into the relevant columns but as you do it you will be writing a macro so that its just question for the next ones of hitting the shortcut keys that you will use. So, click in Cell A2 which should now be cell containing first record, up to Tools, Macro, Record New Macro, assign to keyboard and use Ctrl Shift T and OK, now start the procedure and when you've finished the first one, click on the first icon on the macro toolbar to stop it running. If you want to assign the macro to a button instead of the shortcut keys, draw a rectangle or circle, then right button on the border, add text, and put in macro or whatever you want, then right button fill with colour to make it pretty and lastly, right button, assign to macro. So that in future to run the macro and change the rest of the entries into their separate columns should be a dodle. It's still not a one push button thing, as each record has to be done separately but at least the macro will cover that if you set it up right to begin with. Hope this helps. When it's all sorted into columns, highlight the lot, save for the column headings, up to data,sort and put into chronological order by Surname, and/or whatever else you need to sort by.



Once this is done its easy to do mail merge, you just go into Word start the process, use existing list, and browse to where your Excel list is. If you need help on that, feel free to email me.
?
2017-01-19 11:03:56 UTC
1
2008-01-20 14:34:31 UTC
My understanding is that your column A has one address listed after another after another etc. and you need to move each address into a row spanning several columns, so that you can sort the data before you move it into Word.

As far as I know there is no "instant" way of doing this but you can speed up the process by recording a macro. Look up "record a macro" in Excel Help and follow the instructions. For instance put your cursor in cell A1, start recording the macro and move all the cells up where you want them then delete the blank lines, then stop recording the macro. Assign it a keystoke eg CTRL A.

You will need to record different macros for addresses with 5, 6 or 7 lines, each with a shortcut key.

Now work through your list applying the relevant macro to each address. It means 3 keystrokes or so per address instead of hours and hours of moving cells around.

I hope this helps you.



Incidentally when it comes to moving the data to Word, you can copy and paste it all into Wordpad or Notepad to remove the formatting - then copy the data again and paste into Word. This will mean you don't end up with a massive great table in Word.
2008-01-20 14:22:51 UTC
It all depends how you want the data to appear. There is a formula I used recently that should do the job, but I'd have to know how you wanted to have the data appear (ie in several columns or 1 column or whatever) - if you send me a message with what you want specifically I can adjust the formula.
?
2016-10-05 06:58:40 UTC
With Excel (edition 2010), click on "document" on the menu bar, then decide on help. click on the Microsoft workplace help button, then form "printing labels" in the seek field. you will get a result like this: Create and print mailing labels for an handle checklist in Excel ArticleSend a mass mailing to an handle checklist which you safeguard in an Excel 2010 worksheet good success!
kryan957
2008-01-21 12:51:44 UTC
If I read this right, you want to use the concatenate function to bring the columns together.
Holbolbe
2008-01-20 14:20:26 UTC
New supreme office suite 3.0

has alot of avery size labels.


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Loading...