Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have some data that i want to layout in a different format. At present it
is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 .... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I would think that you would really want:
Name, PropertyTitle, Qty/Number/whatever If you don't want that second column just delete it after you run this macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Dim oCol As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("Name", "Property", "Value") oRow = 1 With CurWks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol)) = "" Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sjn wrote: I have some data that i want to layout in a different format. At present it is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 ... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Awesome. That was perfect. Thanks ;o)
Now for my next question... An extract of some of the data is below: Tanunda 88530(3-5) Tanunda 885609 Tanunda 88561(0-4) Tanunda 88562(0-4) Tanunda 885673 Tanunda 88568(6-9) Tanunda 885923 I need to break that out to be: Tanunda 885303 Tanunda 885304 Tanunda 885305 Tanunda 885609 Tanunda 885610 Tanunda 885611 Tanunda 885612 Tanunda 885613 Tanunda 885614 Tanunda 885620 Tanunda 885621 Tanunda 885622 Tanunda 885623 Tanunda 885624 Tanunda 885673 Tanunda 885686 Tanunda 885687 Tanunda 885688 Tanunda 885689 Tanunda 885923 Maybe it will be easier to do this as part of the first step? Cheers Steve "Dave Peterson" wrote: I would think that you would really want: Name, PropertyTitle, Qty/Number/whatever If you don't want that second column just delete it after you run this macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Dim oCol As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("Name", "Property", "Value") oRow = 1 With CurWks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol)) = "" Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sjn wrote: I have some data that i want to layout in a different format. At present it is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 ... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
So you only used column A:B (deleting the column B that the macro created)?
Option Explicit Sub Testme() 'no change to the old code until you get to the bottom.... '.... End With NewWks.UsedRange.Columns.AutoFit newwks.range("B1").entirecolumn.delete Call testme02(newwks) End Sub Sub testme02(CurWks As Worksheet) Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Dim OpenParenPos As Long Dim mySplit As Variant Dim myValue As Variant Dim myPrefix As Variant Dim HowMany As Long Dim iCtr As Long Dim StartValue As Long Dim EndValue As Long Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 2).Value _ = Array("Name", "Value") oRow = 2 With CurWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow myValue = .Cells(iRow, "B").Value OpenParenPos = InStr(1, myValue, "(", vbTextCompare) If OpenParenPos = 0 Then NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = myValue oRow = oRow + 1 Else myPrefix = Left(myValue, OpenParenPos - 1) myValue = Mid(myValue, OpenParenPos + 1, 255) 'chop final close paren myValue = Left(myValue, Len(myValue) - 1) mySplit = Split(myValue, "-") If (UBound(mySplit) - LBound(mySplit)) < 1 Then MsgBox "error in: " & iRow & "'s column B data" _ & "Process stopped" Exit Sub End If 'no validation here! StartValue = mySplit(LBound(mySplit)) EndValue = mySplit(UBound(mySplit)) HowMany = EndValue - StartValue + 1 NewWks.Cells(oRow, "A").Resize(HowMany).Value _ = .Cells(iRow, "A").Value For iCtr = StartValue To EndValue Step 1 NewWks.Cells(oRow, "B").Value _ = myPrefix * 10 + iCtr oRow = oRow + 1 Next iCtr End If Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub sjn wrote: Awesome. That was perfect. Thanks ;o) Now for my next question... An extract of some of the data is below: Tanunda 88530(3-5) Tanunda 885609 Tanunda 88561(0-4) Tanunda 88562(0-4) Tanunda 885673 Tanunda 88568(6-9) Tanunda 885923 I need to break that out to be: Tanunda 885303 Tanunda 885304 Tanunda 885305 Tanunda 885609 Tanunda 885610 Tanunda 885611 Tanunda 885612 Tanunda 885613 Tanunda 885614 Tanunda 885620 Tanunda 885621 Tanunda 885622 Tanunda 885623 Tanunda 885624 Tanunda 885673 Tanunda 885686 Tanunda 885687 Tanunda 885688 Tanunda 885689 Tanunda 885923 Maybe it will be easier to do this as part of the first step? Cheers Steve "Dave Peterson" wrote: I would think that you would really want: Name, PropertyTitle, Qty/Number/whatever If you don't want that second column just delete it after you run this macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Dim oCol As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("Name", "Property", "Value") oRow = 1 With CurWks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol)) = "" Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sjn wrote: I have some data that i want to layout in a different format. At present it is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 ... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) -- Dave Peterson -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Split was added in xl2k.
If you're using xl97, you can add this function: Function Split97(sStr As String, sdelim As String) As Variant 'from Tom Ogilvy Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function And change this line: mySplit = Split(myValue, "-") to mySplit = Split97(myValue, "-") Dave Peterson wrote: So you only used column A:B (deleting the column B that the macro created)? Option Explicit Sub Testme() 'no change to the old code until you get to the bottom.... '.... End With NewWks.UsedRange.Columns.AutoFit newwks.range("B1").entirecolumn.delete Call testme02(newwks) End Sub Sub testme02(CurWks As Worksheet) Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Dim OpenParenPos As Long Dim mySplit As Variant Dim myValue As Variant Dim myPrefix As Variant Dim HowMany As Long Dim iCtr As Long Dim StartValue As Long Dim EndValue As Long Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 2).Value _ = Array("Name", "Value") oRow = 2 With CurWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow myValue = .Cells(iRow, "B").Value OpenParenPos = InStr(1, myValue, "(", vbTextCompare) If OpenParenPos = 0 Then NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = myValue oRow = oRow + 1 Else myPrefix = Left(myValue, OpenParenPos - 1) myValue = Mid(myValue, OpenParenPos + 1, 255) 'chop final close paren myValue = Left(myValue, Len(myValue) - 1) mySplit = Split(myValue, "-") If (UBound(mySplit) - LBound(mySplit)) < 1 Then MsgBox "error in: " & iRow & "'s column B data" _ & "Process stopped" Exit Sub End If 'no validation here! StartValue = mySplit(LBound(mySplit)) EndValue = mySplit(UBound(mySplit)) HowMany = EndValue - StartValue + 1 NewWks.Cells(oRow, "A").Resize(HowMany).Value _ = .Cells(iRow, "A").Value For iCtr = StartValue To EndValue Step 1 NewWks.Cells(oRow, "B").Value _ = myPrefix * 10 + iCtr oRow = oRow + 1 Next iCtr End If Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub sjn wrote: Awesome. That was perfect. Thanks ;o) Now for my next question... An extract of some of the data is below: Tanunda 88530(3-5) Tanunda 885609 Tanunda 88561(0-4) Tanunda 88562(0-4) Tanunda 885673 Tanunda 88568(6-9) Tanunda 885923 I need to break that out to be: Tanunda 885303 Tanunda 885304 Tanunda 885305 Tanunda 885609 Tanunda 885610 Tanunda 885611 Tanunda 885612 Tanunda 885613 Tanunda 885614 Tanunda 885620 Tanunda 885621 Tanunda 885622 Tanunda 885623 Tanunda 885624 Tanunda 885673 Tanunda 885686 Tanunda 885687 Tanunda 885688 Tanunda 885689 Tanunda 885923 Maybe it will be easier to do this as part of the first step? Cheers Steve "Dave Peterson" wrote: I would think that you would really want: Name, PropertyTitle, Qty/Number/whatever If you don't want that second column just delete it after you run this macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Dim oCol As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("Name", "Property", "Value") oRow = 1 With CurWks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol)) = "" Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sjn wrote: I have some data that i want to layout in a different format. At present it is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 ... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Can't thank you enough for that. Saved me loads of work!!
Cheers Steve "Dave Peterson" wrote: So you only used column A:B (deleting the column B that the macro created)? Option Explicit Sub Testme() 'no change to the old code until you get to the bottom.... '.... End With NewWks.UsedRange.Columns.AutoFit newwks.range("B1").entirecolumn.delete Call testme02(newwks) End Sub Sub testme02(CurWks As Worksheet) Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Dim OpenParenPos As Long Dim mySplit As Variant Dim myValue As Variant Dim myPrefix As Variant Dim HowMany As Long Dim iCtr As Long Dim StartValue As Long Dim EndValue As Long Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 2).Value _ = Array("Name", "Value") oRow = 2 With CurWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow myValue = .Cells(iRow, "B").Value OpenParenPos = InStr(1, myValue, "(", vbTextCompare) If OpenParenPos = 0 Then NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = myValue oRow = oRow + 1 Else myPrefix = Left(myValue, OpenParenPos - 1) myValue = Mid(myValue, OpenParenPos + 1, 255) 'chop final close paren myValue = Left(myValue, Len(myValue) - 1) mySplit = Split(myValue, "-") If (UBound(mySplit) - LBound(mySplit)) < 1 Then MsgBox "error in: " & iRow & "'s column B data" _ & "Process stopped" Exit Sub End If 'no validation here! StartValue = mySplit(LBound(mySplit)) EndValue = mySplit(UBound(mySplit)) HowMany = EndValue - StartValue + 1 NewWks.Cells(oRow, "A").Resize(HowMany).Value _ = .Cells(iRow, "A").Value For iCtr = StartValue To EndValue Step 1 NewWks.Cells(oRow, "B").Value _ = myPrefix * 10 + iCtr oRow = oRow + 1 Next iCtr End If Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub sjn wrote: Awesome. That was perfect. Thanks ;o) Now for my next question... An extract of some of the data is below: Tanunda 88530(3-5) Tanunda 885609 Tanunda 88561(0-4) Tanunda 88562(0-4) Tanunda 885673 Tanunda 88568(6-9) Tanunda 885923 I need to break that out to be: Tanunda 885303 Tanunda 885304 Tanunda 885305 Tanunda 885609 Tanunda 885610 Tanunda 885611 Tanunda 885612 Tanunda 885613 Tanunda 885614 Tanunda 885620 Tanunda 885621 Tanunda 885622 Tanunda 885623 Tanunda 885624 Tanunda 885673 Tanunda 885686 Tanunda 885687 Tanunda 885688 Tanunda 885689 Tanunda 885923 Maybe it will be easier to do this as part of the first step? Cheers Steve "Dave Peterson" wrote: I would think that you would really want: Name, PropertyTitle, Qty/Number/whatever If you don't want that second column just delete it after you run this macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim FirstCol As Long Dim LastCol As Long Dim oRow As Long Dim oCol As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 3).Value _ = Array("Name", "Property", "Value") oRow = 1 With CurWks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol)) = "" Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm sjn wrote: I have some data that i want to layout in a different format. At present it is as follows: Name, Property1, Property2, ... Property172 abc, 786 7684 8965 abd 645 64573 64328 ... (there are 2007 rows) although there are 172 columns there may not necessarily be an entry in every column for each name. I want it to be in the format Name, Property abc, 786 abc, 7684 abc, 8965 abd, 645 abd, 64573 abd, 64328 Can anybody help? Thanks ;o) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Find data in columns, then place in rows | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Putting data into pivot tables in columns not rows. | Excel Discussion (Misc queries) | |||
coverting rows to columns | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |