Forum Discussion
Help moving multiple non adjacent excel rows up one row
I have been given a spreadsheet with names and addresses. Problem is the names are below the addresses. Luckily all the names have an (H) beside them so I do have that. Is there a way through formula or script to choose all those cells and move them up one row, placing them on top of the street address?
Thank you!
5 Replies
Although Abiola's solution, or a variation of it, could work with some interpretations of your situation, I thought it would be helpful to clarify exactly what it is you're looking at.
You say "the names are below the addresses" but as a stickler for precision with descriptions and use of words, I thought I'd show you at least three different layouts that in my mind you could be describing. See below:
These are three different layouts with "the names below the addresses", and only in the third case would Abiola's suggestion work.
I sincerely hope your actual layout is the second or third, because those are the easiest to resolve via a formula.
Another consideration: I've seen address files created for readability rather than designed as a good Excel Table. The latter would look have an entire address on one row for one person, and the table would have column headings like this:
That enables you to do all kinds of things at the output end...which we'll get into later, after we resolve your first question. So DO let us know the layout of your existing address file.
Here is the layout I have. It is all in one column.
I need to have the names above the address. There are 177 of them so I would rather not do this manually. All recipient names have the (H) next to them so i have a primary key. Fell like I have the info needed just not sure how to execute.
You do NOT really want the name above the address. You should not want the name above the address. That would be a poor table or poor database. It would look like you expect addresses to look, so I understand why you might think you want it that way. But this is not an output sheet (if it were, it would already be laid out that way).
You want (or should want) the name to the left of the street address. And then, to the right, will be city. To the right of that, the zip. And so forth. (Where are those additional data items, by the way?)
From that (with appropriate headings) you'd be able to print address labels via mail merge with Word, print address lists, whatever....
All of that preaching done, here in the attached is a formula that will get the name to the left of the street address (or, if you insist, on top) But the truth is, you're still going to have clean up to do. That original layout was just wrong, so it might just make sense to devote the time to fix it. This formula will get you started.
FWIW, I've been there, done that, with other address files that have also not been created in solid Excel table fashion. It's a pain. Once you've moved the name, you can highlight a row (or rows) and delete them; then use Ctrl-Y to repeat the deletion with other highlighted rows. That makes the manual task move more quickly.
But you really should create an address database in the form of an Excel Table...single row per person, single cell per single data item. Then use Excel's abilities in sifting, sorting, mail-merging with Word...the whole world of flexible applications opens up.
- Hello,
You can simply use CTRL + C to Copy and CTRL + V to Paste to desired location- Thank you for your response. There are 177 of them so I am trying not to have to do it manually.
