| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| how to split data from 1 row into two rows continuously | Excel Worksheet Functions | |||
| Unhiding rows using data validation | Excel Worksheet Functions | |||
| inserting rows through external data source | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Linear Mode
