moving data

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I need to move data in the same column on different rows
to the same row in different columns.

ex.

from this:

joe blow
2222 n blow st
blowsville BL 00000

to this:

joe blow 2222 n blow st blowsville BL 00000

I have about 12000 rows of data, so the ole cut & paste
isn't gonna happen. Does anyone know of a better way?

TIA!
Mike
 
One way:

Assuming that you have a blank line in between records, and that
each record is three lines long, put this on a new sheet:

A1: =INDEX(Sheet1!$A:$A,(ROW()-1)*4+COLUMN())

Copy across to C1, then copy A1:C1 down as far as necessary

When done, copy columns A:C on the new sheet and paste special,
selecting the Values radio button.
 
that works, but unfortunately I can't cut & paste that
way and I need to move the data (not copy) and also I
can't move multiple records.
 
I'm not sure I understand the last part,

"When done, copy columns A:C on the new sheet and paste
special,
selecting the Values radio button."
Click to expand...

Am I supposed to copy the data from sheet1? Can you
clarify a little bit? Thanks!
 
on your destination sheet, select columns A:C. Choose Edit/Copy.
Choose Edit/Paste Special, selecting the Values radio button.

That way the formulae will be converted into values.
 
I need to move data in the same column on different
rows
Click to expand...

Try the TRANSPOSE function. Assume your first address is in A1:A3, while
succeeding addresses are in A4:A6, A7:A9, etc. Select the cells B1:D1.
Enter the formula =TRANSPOSE(A1:A3) and array-enter (control-shift-enter).

Now select B1:D1, copy and paste to B4. And here's the tricky part. Put
some dummy text (perhaps a space?) in B2, B3, B5, and B6. Now select your
12k rows in columns B, C, and D. Edit | Fill | Series | click the
AutoFill radio button.

Now you have the reorganized data. Edit | Goto | Special button |
formulas will select your newly arranged addresses. Copy the data, then
Paste Special | values to get rid of the blank rows. Get rid of columns
A-D.
 
Back
Top