Creating a table within a table

U

urlocaljeweler

I am using Excel 2007.

I have 5 columns as illustrated below - (Table Description has no rows yet)

CTwt. Size Metal Jewelers cost Table Description

5 10 Tungsten 31
4 10.5 Tungsten 35
2 11 Tungsten 50
10 11.5 Tungsten 20
8 12 Tungsten 15
5 12.5 Tungsten 50
6 13 Tungsten 45

What I need to accomplish: Column 5(Table Description) needs to be a table
in each row. For example Column 5, Row 1: (new table with 2 columns and 4
rows) like this:

Ctwt. 5
Size 10
Metal Tungsten
Jewelers Cost 31

I need to do this with hundreds of rows. After this I will somehow convert
the table into html format and upload the excel file to my webpage....

Any ideas?

Thank you.
 
E

Eduardo

Hi,
not quiet sure what you need, did you try to select the range, copy, paste
special, transpose
 
J

JLatham

I'm not sure which way you really want to go with:
1) one row with the initial entries along with 3 additional rows to hold the
added information in separate cells before beginning the next existing group,
or
2) one row for everything, with the combined description text in a single
cell on the same row.

So I have written up code that will let you decide for yourself.

The "IntoNewRows()" routine will take care of option 1, and the
"IntoOneCell()" routine takes care of the second option. The
"RemoveAddedRows()" routine can be used to delete the added rows inserted if
you use the 1st method.

To put the code into your workbook, open the workbook, press [Alt]+[F11] to
open the VB Editor. In the VB Editor use Insert --> Module and then copy the
code below and paste it into the code module presented to you. You can
modify things like the column width I set for column E and the number of
blanks in the various entries to try to get the layout out you want.

To run the code, go to the [Developer] tab and click the "Macros" icon and
choose the macro to run. If you do not see the [Developer] tab, use:
Office Button --> [Excel Options] and in the Popular group, choose to "Show
Developer tab in the Ribbon".

Sub IntoOneCell()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long
Dim theDescription As String

RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
theDescription = _
Range("A1") & String(14, " ") & anyCell & vbLf
theDescription = theDescription & _
Range("B1") & String(14, " ") & anyCell.Offset(0, 1) & vbLf
theDescription = theDescription & _
Range("C1") & String(2, " ") & anyCell.Offset(0, 2) & vbLf
theDescription = theDescription & _
Range("D1") & String(2, " ") & anyCell.Offset(0, 3)
anyCell.Offset(0, 4) = theDescription
End If
Next
Set initialRange = Nothing
End Sub

Sub IntoNewRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long

RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
Next
Set initialRange = Nothing
'get new range
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
anyCell.Offset(0, 4) = Range("A1") & String(9, " ") & anyCell
anyCell.Offset(1, 4) = Range("B1") & String(9, " ") &
anyCell.Offset(0, 1)
anyCell.Offset(2, 4) = Range("C1") & String(9, " ") &
anyCell.Offset(0, 2)
anyCell.Offset(3, 4) = Range("D1") & String(2, " ") &
anyCell.Offset(0, 3)
End If
Next
Set initialRange = Nothing
End Sub

Sub RemoveAddedRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long

Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
If IsEmpty(initialRange.Cells(LC, 1)) Then
initialRange.Cells(LC, 1).EntireRow.Delete
End If
Next
Set initialRange = Nothing
End Sub
 
M

Max

Thought that was a marvellous, super response. My hunch is that your Sub
IntoOneCell() does exactly what OP wanted, albeit s/he has yet to reply.
Thanks for sharing your subs with us!
 
U

urlocaljeweler

I apologize! That code works great! Now all I have to do is figure out how
to convert that cell into html!

Thank you very much!

JLatham said:
I'm not sure which way you really want to go with:
1) one row with the initial entries along with 3 additional rows to hold the
added information in separate cells before beginning the next existing group,
or
2) one row for everything, with the combined description text in a single
cell on the same row.

So I have written up code that will let you decide for yourself.

The "IntoNewRows()" routine will take care of option 1, and the
"IntoOneCell()" routine takes care of the second option. The
"RemoveAddedRows()" routine can be used to delete the added rows inserted if
you use the 1st method.

To put the code into your workbook, open the workbook, press [Alt]+[F11] to
open the VB Editor. In the VB Editor use Insert --> Module and then copy the
code below and paste it into the code module presented to you. You can
modify things like the column width I set for column E and the number of
blanks in the various entries to try to get the layout out you want.

To run the code, go to the [Developer] tab and click the "Macros" icon and
choose the macro to run. If you do not see the [Developer] tab, use:
Office Button --> [Excel Options] and in the Popular group, choose to "Show
Developer tab in the Ribbon".

Sub IntoOneCell()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long
Dim theDescription As String

RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
theDescription = _
Range("A1") & String(14, " ") & anyCell & vbLf
theDescription = theDescription & _
Range("B1") & String(14, " ") & anyCell.Offset(0, 1) & vbLf
theDescription = theDescription & _
Range("C1") & String(2, " ") & anyCell.Offset(0, 2) & vbLf
theDescription = theDescription & _
Range("D1") & String(2, " ") & anyCell.Offset(0, 3)
anyCell.Offset(0, 4) = theDescription
End If
Next
Set initialRange = Nothing
End Sub

Sub IntoNewRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long

RemoveAddedRows ' cleanup just in case
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
initialRange.Cells(LC, 1).EntireRow.Insert
Next
Set initialRange = Nothing
'get new range
Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
Columns("E:E").ColumnWidth = 20
For Each anyCell In initialRange
If Not IsEmpty(anyCell) Then
'start a group
anyCell.Offset(0, 4) = Range("A1") & String(9, " ") & anyCell
anyCell.Offset(1, 4) = Range("B1") & String(9, " ") &
anyCell.Offset(0, 1)
anyCell.Offset(2, 4) = Range("C1") & String(9, " ") &
anyCell.Offset(0, 2)
anyCell.Offset(3, 4) = Range("D1") & String(2, " ") &
anyCell.Offset(0, 3)
End If
Next
Set initialRange = Nothing
End Sub

Sub RemoveAddedRows()
Dim initialRange As Range
Dim anyCell As Range
Dim LC As Long

Set initialRange = _
ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False
For LC = initialRange.Cells.Count To 2 Step -1
If IsEmpty(initialRange.Cells(LC, 1)) Then
initialRange.Cells(LC, 1).EntireRow.Delete
End If
Next
Set initialRange = Nothing
End Sub


urlocaljeweler said:
I am using Excel 2007.

I have 5 columns as illustrated below - (Table Description has no rows yet)

CTwt. Size Metal Jewelers cost Table Description

5 10 Tungsten 31
4 10.5 Tungsten 35
2 11 Tungsten 50
10 11.5 Tungsten 20
8 12 Tungsten 15
5 12.5 Tungsten 50
6 13 Tungsten 45

What I need to accomplish: Column 5(Table Description) needs to be a table
in each row. For example Column 5, Row 1: (new table with 2 columns and 4
rows) like this:

Ctwt. 5
Size 10
Metal Tungsten
Jewelers Cost 31

I need to do this with hundreds of rows. After this I will somehow convert
the table into html format and upload the excel file to my webpage....

Any ideas?

Thank you.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a table and automating it throughout cells 2
Minimizing Rows of with similar data 3
Creating multiple html tables for products 2
Product descriptions easily made? 1
Formula for formatting rows and columns minimizing the outcome. 1
Formula for taking text out of column B and placing it into column 1
I need help with a table formula/mass editting 2
inserting table data from a Word document into an Access table 7

Top