Need to convert rows of data into columns..

[andycapp28]

I have an excel sheet holding information in rows.

Some of the 21 rows need to become 21 columns. for example
cell I2 needs to become G1
cell I3 needs to become H1

The 21 row values in column j need to be kept in step. for example
cell J2 needs to become G2
cell J3 needs to become H2

columns a-f are repeated for each of the set of 21 rows. I need to know if such a thing is possible and how to write a macro to do it.

From this test data I hope you can understand what I need to try and do.

a b c d e f g h i j
60553 SY U103 12/7/99 SVY 11/30/99 1 8 Times 6 or more
60553 SY U103 12/7/99 SVY 11/30/99 2 8 Vacation Cruise
60555 SY W101 12/7/99 SVY 11/30/99 1 8 Times Twice
60555 SY W101 12/7/99 SVY 11/30/99 2 13 Vacation Factors

what I need is upon change of values in column a

a b c d e f Times Vacation
60553 SY U103 12/7/99 SVY 11/30/99 6 or more Cruise
60555 SY W101 12/7/99 SVY 11/30/99 Twice Factors

regards
AC

 

[SkipVought]

Hi,

Have to do TOO MUCH GUESSING at what datas associates with what columns.

Please search this page for TGML Link and use the TT tag to align your examples like...
[tt]
a b c d e f g h i j
60553 SY U103 12/7/99 SVY 11/30/99 1 8 Times 6 or more
60553 SY U103 12/7/99 SVY 11/30/99 2 8 Vacation Cruise
60555 SY W101 12/7/99 SVY 11/30/99 1 8 Times Twice
60555 SY W101 12/7/99 SVY 11/30/99 2 13 Vacation Factors
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarduspater]

For example,if cell J2 needs to become G2 and cell J3 needs to become H2:

Range("J2:J3").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True



Candu si tene su 'entu es prezisu bentolare.

 

[andycapp28]

Skip,

I do not seem to be finding any examples of how to tag data in a thread?

I need every 21 rows from the spreadsheet converted to a single row.
Keeping columns a to f as is.
Then column g to aa is to be made up of the row values from the existing column i.
Then 21 rows underneath the columns g to aa of data that is from the existing column j

for example
single row
a to e as is
cell g2 moves to cell g1
cell g3 moves to cell h1
cell g4 moves to cell i1
cell g5 moves to cell j1

cell h2 moves to cell g2
cell h3 moves to cell h2
cell h4 moves to cell i2
cell h5 moves to cell j2

If this is still not clear then I'm afraid I'm at a loss now how to define the requirement.

Thanks in hope
AC

 

[randy700]

Andy,

Directly below the window you are typing your response in,
you will see Process TGML. Select it to provide your
data as Skip has requested.

For example:
[ignore]

Data here

[/ignore]
will appear as:

Data here

Randy

 

[sarduspater]

Try this:


Option Explicit

Sub xxx()
transpose Range("G2:G5"), Range("G1:J1")
End Sub

Function transpose(f As Range, t As Range)
Dim i As Integer
For i = 1 To f.Count - 1
t.Item(i) = f.Item(i)
Next
End Function

Candu si tene su 'entu es prezisu bentolare.

 

[PHV]

Anyway, how is this post related to ms-access ?
Post here: forum707