| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing based on cell value
Hi,
I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
A few questions:
Are there multiple Date, Name, and Shifts? Are the lines variable after the Categories? (perhaps 50 under A but only 25 under B) Is there a space between the last line in A and the first line for B? I need a clearer picture to give some useful ideas. Thanks! -SA "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Karim,
All this data is in the first column? Is it all the same pattern Date,Name,Shift,CategoryA,CategoryB-then repeat? "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
there are multiple dates (every year day)
four or five different names and three shifts the lines after the categories are variables and are not fixed by category too: so it can be 5 lines for a specific date/category and 10 lines for other date/category Hope that this clarified the picture a bit Thanks, Karim "StumpedAgain" wrote: A few questions: Are there multiple Date, Name, and Shifts? Are the lines variable after the Categories? (perhaps 50 under A but only 25 under B) Is there a space between the last line in A and the first line for B? I need a clearer picture to give some useful ideas. Thanks! -SA "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
All the data is in the first column. the pattern repeats, however the pattern
size is different because the number of lines per category varies. There are blank lines between categories Thanks, Karim "JR Form" wrote: Karim, All this data is in the first column? Is it all the same pattern Date,Name,Shift,CategoryA,CategoryB-then repeat? "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
I forgot to mention that there is a space between categories
"StumpedAgain" wrote: A few questions: Are there multiple Date, Name, and Shifts? Are the lines variable after the Categories? (perhaps 50 under A but only 25 under B) Is there a space between the last line in A and the first line for B? I need a clearer picture to give some useful ideas. Thanks! -SA "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Hi Karim,
I'm working on a solution and have another question. Do you want the Date, Name, and Shift to be copied down to every line or just on the first row under the category headers? Thanks for the clarification. -SA "karim" wrote: I forgot to mention that there is a space between categories "StumpedAgain" wrote: A few questions: Are there multiple Date, Name, and Shifts? Are the lines variable after the Categories? (perhaps 50 under A but only 25 under B) Is there a space between the last line in A and the first line for B? I need a clearer picture to give some useful ideas. Thanks! -SA "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Hi SA,
It would be nice if they are copie down to every line. if this will complicate things, I can do it manually Thank you very much Karim "StumpedAgain" wrote: Hi Karim, I'm working on a solution and have another question. Do you want the Date, Name, and Shift to be copied down to every line or just on the first row under the category headers? Thanks for the clarification. -SA "karim" wrote: I forgot to mention that there is a space between categories "StumpedAgain" wrote: A few questions: Are there multiple Date, Name, and Shifts? Are the lines variable after the Categories? (perhaps 50 under A but only 25 under B) Is there a space between the last line in A and the first line for B? I need a clearer picture to give some useful ideas. Thanks! -SA "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
OK, so the following works for what I have set up. Because I can't see what
you have exactly, I would recommend saving a backup. ;) Let me know if it doesn't work! -SA PS. Mind the text wrapping. Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Hi SA,
this code does the first two rows and give me an invalide procedure call at this line: startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) (I did fix the text wraping) Thanks, Karim "StumpedAgain" wrote: OK, so the following works for what I have set up. Because I can't see what you have exactly, I would recommend saving a backup. ;) Let me know if it doesn't work! -SA PS. Mind the text wrapping. Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
I'm gone for the night starting in 2min. I'll check back tomorrow and see if
you've figured anything out or if someone else has looked at it. Until then... :) "karim" wrote: Hi SA, this code does the first two rows and give me an invalide procedure call at this line: startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) (I did fix the text wraping) Thanks, Karim "StumpedAgain" wrote: OK, so the following works for what I have set up. Because I can't see what you have exactly, I would recommend saving a backup. ;) Let me know if it doesn't work! -SA PS. Mind the text wrapping. Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Hi SA,
I couldn't fix it and it only does the first two rows and stops with the error message "invalid procedure call" Thanks again Karim "StumpedAgain" wrote: I'm gone for the night starting in 2min. I'll check back tomorrow and see if you've figured anything out or if someone else has looked at it. Until then... :) "karim" wrote: Hi SA, this code does the first two rows and give me an invalide procedure call at this line: startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) (I did fix the text wraping) Thanks, Karim "StumpedAgain" wrote: OK, so the following works for what I have set up. Because I can't see what you have exactly, I would recommend saving a backup. ;) Let me know if it doesn't work! -SA PS. Mind the text wrapping. Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Hmm... I'm not sure what the problem is. I'll start a new post and see if
someone can't enlighten us. :) "karim" wrote: Hi SA, I couldn't fix it and it only does the first two rows and stops with the error message "invalid procedure call" Thanks again Karim "StumpedAgain" wrote: I'm gone for the night starting in 2min. I'll check back tomorrow and see if you've figured anything out or if someone else has looked at it. Until then... :) "karim" wrote: Hi SA, this code does the first two rows and give me an invalide procedure call at this line: startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) (I did fix the text wraping) Thanks, Karim "StumpedAgain" wrote: OK, so the following works for what I have set up. Because I can't see what you have exactly, I would recommend saving a backup. ;) Let me know if it doesn't work! -SA PS. Mind the text wrapping. Option Explicit Sub Save_Time() Dim glcount, j, m, n As Integer Dim startspot, nextspot As Range j = 0 Set startspot = Range("A1") 'or wherever you start Set nextspot = Range("A1") Do Set startspot = startspot.Offset(j, 0) If nextspot = "" Then Exit Do nextspot.Select ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1) ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0) ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value, Len(startspot.Value) - 6) startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value, Len(startspot.Value) - 6) startspot.Value = Left(startspot.Value, 4) startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4) startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value, Len(startspot.Offset(0, 2).Value) - 7) startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5) Set nextspot = ActiveCell.Offset(4, 0) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(m, 1).Select Selection.Cut Destination:=startspot.Offset(0, 3) nextspot.Select Set nextspot = ActiveCell.End(xlDown).Offset(2, 0) With ActiveCell n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count End With ActiveCell.Resize(n, 1).Select Selection.Cut Destination:=startspot.Offset(0, 4) j = j + Application.Max(n, m) Loop End Sub |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Karim,
One other step I left out. Step 1.5 place the word "Stop" at row 30001 "karim" wrote: All the data is in the first column. the pattern repeats, however the pattern size is different because the number of lines per category varies. There are blank lines between categories Thanks, Karim "JR Form" wrote: Karim, All this data is in the first column? Is it all the same pattern Date,Name,Shift,CategoryA,CategoryB-then repeat? "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
Karim-Did you get a solution for this?
"karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
I inquired about what was wrong with my macro but got a different solution
instead. From Dave Peterson: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim FirstRow As Long Dim oRow As Long Dim BigRng As Range Dim SmallArea As Range Dim NextGroupMustBeFirstCategory As Boolean Dim RngToCopy As Range Dim oCol As Long Dim LinesPerGroup As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add With CurWks FirstRow = 1 Set BigRng = .Range(.Cells(FirstRow, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) oRow = 0 LinesPerGroup = 1 For Each SmallArea In BigRng.Areas If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then 'This is the Date/name/shift group 'Start of a new group. oRow = oRow + LinesPerGroup NewWks.Cells(oRow, "A").Resize(1, 5).Value _ = Array("Date", "Name", "Shift", "Category A", "Category B") oRow = oRow + 1 'remove "Date: " NewWks.Cells(oRow, "A").Value _ = Trim(Mid(SmallArea.Cells(1, 1).Value, 6)) 'remove "Name: " NewWks.Cells(oRow, "B").Value _ = Trim(Mid(SmallArea.Cells(2, 1).Value, 6)) 'remove "Shift: " NewWks.Cells(oRow, "C").Value _ = Trim(Mid(SmallArea.Cells(3, 1).Value, 7)) NextGroupMustBeFirstCategory = True LinesPerGroup = 3 Else 'This is the category A or Category B section. With SmallArea If .Cells.Count LinesPerGroup Then LinesPerGroup = .Cells.Count End If Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) End With If NextGroupMustBeFirstCategory Then oCol = 4 'column D 'get ready for the category B group NextGroupMustBeFirstCategory = False Else oCol = 5 'column E End If RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial End If Next SmallArea End With Application.CutCopyMode = False NewWks.UsedRange.Columns.AutoFit End Sub Seems to work well for what I have set up. Hope it helps! "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
http://www.microsoft.com/office/comm...6-e9cfaea042aa
In case you want to see the origioinal text from Dave. |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transposing based on cell value
thanks SA
JR "StumpedAgain" wrote: I inquired about what was wrong with my macro but got a different solution instead. From Dave Peterson: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim FirstRow As Long Dim oRow As Long Dim BigRng As Range Dim SmallArea As Range Dim NextGroupMustBeFirstCategory As Boolean Dim RngToCopy As Range Dim oCol As Long Dim LinesPerGroup As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add With CurWks FirstRow = 1 Set BigRng = .Range(.Cells(FirstRow, "A"), _ .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeConstants) oRow = 0 LinesPerGroup = 1 For Each SmallArea In BigRng.Areas If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then 'This is the Date/name/shift group 'Start of a new group. oRow = oRow + LinesPerGroup NewWks.Cells(oRow, "A").Resize(1, 5).Value _ = Array("Date", "Name", "Shift", "Category A", "Category B") oRow = oRow + 1 'remove "Date: " NewWks.Cells(oRow, "A").Value _ = Trim(Mid(SmallArea.Cells(1, 1).Value, 6)) 'remove "Name: " NewWks.Cells(oRow, "B").Value _ = Trim(Mid(SmallArea.Cells(2, 1).Value, 6)) 'remove "Shift: " NewWks.Cells(oRow, "C").Value _ = Trim(Mid(SmallArea.Cells(3, 1).Value, 7)) NextGroupMustBeFirstCategory = True LinesPerGroup = 3 Else 'This is the category A or Category B section. With SmallArea If .Cells.Count LinesPerGroup Then LinesPerGroup = .Cells.Count End If Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) End With If NextGroupMustBeFirstCategory Then oCol = 4 'column D 'get ready for the category B group NextGroupMustBeFirstCategory = False Else oCol = 5 'column E End If RngToCopy.Copy NewWks.Cells(oRow, oCol).PasteSpecial End If Next SmallArea End With Application.CutCopyMode = False NewWks.UsedRange.Columns.AutoFit End Sub Seems to work well for what I have set up. Hope it helps! "karim" wrote: Hi, I have 30,000 rows of data (exported from a text file) in the following manner: Date: xxx Name:xxx Shift:xxx Category A line 1 line 2 line 3 etc... Category B line 1 line 2 line 3 etc.. and I want to convert the data into columns: Date Name Shift Category A Category B line 1 line 2 line 3 Any suggestions on how to do that? Thanks in advance Karim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transposing contents of a cell | Excel Discussion (Misc queries) | |||
| Transposing Multiple Cell References to Multiple Values (NOT total | Excel Discussion (Misc queries) | |||
| Transposing Multiple Cell references as Multiple Values | Excel Discussion (Misc queries) | |||
| Transposing | Excel Worksheet Functions | |||
| Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming | |||
Linear Mode
