converting data in colums to rows

[Evad]

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??

 

[Norman Jones]

Hi Evad,

With the data selected, try:

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

 

[Norman Jones]

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

 

[Evad]

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

 

[Norman Jones]

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.

 

[Evad]

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]

Hi Evad,

Are there always four ref columns?

The existing data comprises multiple rows?


---
Regards,
Norman

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

 

[Evad]

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

 

[Norman Jones]

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
'<<=============