ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Coverting data from some columns to rows (but not the primary colu (https://www.excelbanter.com/excel-discussion-misc-queries/65323-coverting-data-some-columns-rows-but-not-primary-colu.html)

sjn
Coverting data from some columns to rows (but not the primary colu
 
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
Coverting data from some columns to rows (but not the primary colu
 
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

sjn
Coverting data from some columns to rows (but not the primary
 
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
Coverting data from some columns to rows (but not the primary
 
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
Coverting data from some columns to rows (but not the primary
 
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

sjn
Coverting data from some columns to rows (but not the primary
 
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


All times are GMT +1. The time now is 08:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com