Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Coverting data from some columns to rows (but not the primary colu
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
sjn
 
Posts: n/a
Default 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)
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
sjn
 
Posts: n/a
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
sjn
 
Posts: n/a
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find data in columns, then place in rows couriced Excel Worksheet Functions 2 September 29th 05 05:44 PM
How to swap rows and columns? [email protected] Excel Discussion (Misc queries) 5 September 21st 05 08:07 AM
Putting data into pivot tables in columns not rows. ACP Excel Discussion (Misc queries) 1 August 24th 05 01:18 PM
coverting rows to columns Rebecca Excel Worksheet Functions 2 August 19th 05 10:50 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 05:55 PM


All times are GMT +1. The time now is 03:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement