converting data in colums to rows
 

I have a sheet that has been setup that shows a list of documents in one
cloumn and 4 rows are dedicated to indicating reference documents. ie

drawing 1, desc 1, desc2, ref1, ref2, ref3, ref4

where ',' represents a different column.

I want to convert this to the following format

drawing 1, desc 1, desc 2, ref1
drawing 1, desc 1, desc 2, ref2
drawing 1, desc 1, desc 2, ref3
drawing 1, desc 1, desc 2, ref4


Any ideas??

converting data in colums to rows
 

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman



"Evad" wrote in message
...
I have a sheet that has been setup that shows a list of documents in one
cloumn and 4 rows are dedicated to indicating reference documents. ie

drawing 1, desc 1, desc2, ref1, ref2, ref3, ref4

where ',' represents a different column.

I want to convert this to the following format

drawing 1, desc 1, desc 2, ref1
drawing 1, desc 1, desc 2, ref2
drawing 1, desc 1, desc 2, ref3
drawing 1, desc 1, desc 2, ref4


Any ideas??

converting data in colums to rows
 

Hi Evad,

There is a typo. The response should have read:

With the data selected, try:

Data | Text to Columns | Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman

converting data in colums to rows
 

My data is not comma delimited. the comma represents a different column.



"Norman Jones" wrote:

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman



"Evad" wrote in message
...
I have a sheet that has been setup that shows a list of documents in one
cloumn and 4 rows are dedicated to indicating reference documents. ie

drawing 1, desc 1, desc2, ref1, ref2, ref3, ref4

where ',' represents a different column.

I want to convert this to the following format

drawing 1, desc 1, desc 2, ref1
drawing 1, desc 1, desc 2, ref2
drawing 1, desc 1, desc 2, ref3
drawing 1, desc 1, desc 2, ref4


Any ideas??

converting data in colums to rows
 

Hi Evad,

My data is not comma delimited. the comma represents a different column

Then try replacing the comma delimiter with one that accords with your data.

If this is not feasible, you would need to provide more information about
the layout of the raw data.


---
Regards,
Norman



"Evad" wrote in message
...
My data is not comma delimited. the comma represents a different column.



"Norman Jones" wrote:

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman

converting data in colums to rows
 

The data is actually in Excel in different colums. see below

A B C D E F
G
drawing 1 | desc 1 | desc2 | ref1 | ref2 | ref3 | ref4 |


I want to convert this to the following format
A B C D
drawing 1 | desc 1 | desc 2 | ref1
drawing 1 | desc 1 | desc 2 | ref2
drawing 1 | desc 1 | desc 2 | ref3
drawing 1 | desc 1 | desc 2 | ref4



"Norman Jones" wrote:

Hi Evad,

My data is not comma delimited. the comma represents a different column

Then try replacing the comma delimiter with one that accords with your data.

If this is not feasible, you would need to provide more information about
the layout of the raw data.


---
Regards,
Norman



"Evad" wrote in message
...
My data is not comma delimited. the comma represents a different column.



"Norman Jones" wrote:

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman

converting data in colums to rows
 

Hi Evad,

Are there always four ref columns?

The existing data comprises multiple rows?


---
Regards,
Norman



"Evad" wrote in message
...
The data is actually in Excel in different colums. see below

A B C D E F
G
drawing 1 | desc 1 | desc2 | ref1 | ref2 | ref3 | ref4 |


I want to convert this to the following format
A B C D
drawing 1 | desc 1 | desc 2 | ref1
drawing 1 | desc 1 | desc 2 | ref2
drawing 1 | desc 1 | desc 2 | ref3
drawing 1 | desc 1 | desc 2 | ref4



"Norman Jones" wrote:

Hi Evad,

My data is not comma delimited. the comma represents a different column

Then try replacing the comma delimiter with one that accords with your
data.

If this is not feasible, you would need to provide more information about
the layout of the raw data.


---
Regards,
Norman



"Evad" wrote in message
...
My data is not comma delimited. the comma represents a different
column.



"Norman Jones" wrote:

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman

converting data in colums to rows
 

the number of reference columns is actually always 8. there are about 5000
rows of data.

"Norman Jones" wrote:

Hi Evad,

Are there always four ref columns?

The existing data comprises multiple rows?


---
Regards,
Norman



"Evad" wrote in message
...
The data is actually in Excel in different colums. see below

A B C D E F
G
drawing 1 | desc 1 | desc2 | ref1 | ref2 | ref3 | ref4 |


I want to convert this to the following format
A B C D
drawing 1 | desc 1 | desc 2 | ref1
drawing 1 | desc 1 | desc 2 | ref2
drawing 1 | desc 1 | desc 2 | ref3
drawing 1 | desc 1 | desc 2 | ref4



"Norman Jones" wrote:

Hi Evad,

My data is not comma delimited. the comma represents a different column

Then try replacing the comma delimiter with one that accords with your
data.

If this is not feasible, you would need to provide more information about
the layout of the raw data.


---
Regards,
Norman



"Evad" wrote in message
...
My data is not comma delimited. the comma represents a different
column.



"Norman Jones" wrote:

Hi Evad,

With the data selected, try:

Data | Text to Columns A Delimited | Next |
Select the Comma delimiter | Finish


---
Regards,
Norman

converting data in colums to rows
 

Hi Evad,

Try:

'=============
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim LRow As Long
Dim i As Long

Application.ScreenUpdating = False

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet2") '<<==== CHANGE

LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = SH.Range("A2:A" & LRow)

For i = LRow To 2 Step -1
With Cells(i, "A")
.Offset(1).Resize(7).EntireRow.Insert
.Resize(8, 3).Value = .Resize(1, 3).Value
.Offset(0, 3).Resize(8, 1).Value = _
Application.Transpose(.Offset(0, 3).Resize(1, 8))
End With
Next i
SH.Columns("E:K").Delete

Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman


"Evad" wrote in message
...
the number of reference columns is actually always 8. there are about
5000
rows of data.

"Norman Jones" wrote:

Hi Evad,

Are there always four ref columns?

The existing data comprises multiple rows?


---
Regards,
Norman