Software

Ask something
Question Locked

copy and Transpose pasting

0 helpful
2 replies

by ericjh

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

FROM PREVIOUS POST:
Put this macro code in the module and assign a short cut key like ctrl a. First copy the data then press ctrlA . The data will transpose.

sub pastetranspose()
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
end sub
Posted: 08/22/2005 @ 07:33 PM (PDT)

Is there a way to use this in the following way?

name address and city for 100 records in one vertical column. I want to copy and paste/transpose the 3 row records to appear in one row for each record, 3 columns across. each name and address starts oin a new row.

2 replies

by robo_dev

You could also concatenate a comma to every third row value in a roundabout way by putting this formula in column b

=IF(INT(ROW()/3)=ROW()/3,",","")

This would give you

name
address
city ,
name
address
city ,

Then if you concatenate in column 3
=CONCATENATE(A1,B1)

name
address
city,

Using a word processor, strip out the CRLFs, yielding:
name address city, name address city,

Then using the same word processor, do a global replace of comma with CRLF to make it vertical

name address city
name address city

Then open with excel, and convert text to columns and you're done.

This discussion is closed to new replies.