concatenate cell data to cell different cell

[desarmo]

I'm using Windows 7 and excel 2010. I have about 35,000 rows. Most of my data in Column 1 is "marked up" in square brackets, such as [title] or [date]. Then column 2 contains the associated data for that row. So if cell A1 contains [title], then B1 contains "The Cat in the Hat". And if A2 contains [date], then B2 contains "1962" (for example). So I have about 8 of these "headings" in square brackets, and then they repeat for a different item. I'm trying to convert this into a more standard table format with each of the 8 headings having its own column, so that each item can only occupy a single row, instead of how it currently is with each item occupying like 8 rows. I was able to use VB to accomplish this and transfer the contents into a standard table format in another worksheet. However, the problem I'm having is that some of the content in column 2 is broken up by paragraphs and then is sitting in column one. My VB script only looped through column 1 looking for the headings in square brackets. When it finds a heading the script copies the column 2 data in the appropriate place in a new worksheet. But my problem is that I don't know how to get data from column that is not marked up into the appropriate cell. Here is an example of what my data looks like. I only used 2 different headings in this example to keep it short, but my real data has like 8 headings. I appreciate any help tremendously.

[title]	the cat in the hat -book 1
[content]	this is the content. but sometimes it spills over into column 1, but not this time.
[title]	new book PROBLEM - book 2
[content]	here is some more content. this time, you can see how
it goes into column one,
even though it belongs to the title "new book PROBLEM" book 2
[title]	huckleberry finn - book 3
[content]	the content for book 3

<tbody>
</tbody>

here is how I would like the data to finally be in a new worksheet in the same workbook:

the cat in the hat -book 1	this is the content. but sometimes it spills over into column 1, but not this time.
new book PROBLEM - book 2	here is some more content. this time, you can see how it goes into column one, even though it belongs to the title "new book PROBLEM" book 2
huckleberry finn - book 3	the content for book 3

<tbody>
</tbody>

 

[AlphaFrog]

Can you post your code?

It seems it would be a simple addition to test if the value in column 1 is not a heading (no brackets) and follows [Content], it should be concatenated to the previous content

 

[desarmo]

Can you post your code?

It seems it would be a simple addition to test if the value in column 1 is not a heading (no brackets) and follows [Content], it should be concatenated to the previous content

BEGIN DESARMO COMMENT
I'm really a rookie, so there is probably a more efficient way to code this, but this code does get it done, except the problem I mentioned before when extra data from column 2 is sitting in column 1.

Sub data()
   Dim i, j, k, l As Integer
   j = 2
   For i = 1 To 35735
       For m = 0 To 49
           If Worksheets("original").Cells(i, 1) = "[" & m & "] " Then
                For k = 1 To 80
                        If Worksheets("original").Cells(i + k, 1) = "[id] " Then
                           Worksheets("newWorksheet").Cells(j, 1) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Subject] " Then
                           Worksheets("newWorksheet").Cells(j, 2) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Content] " Then
                           Worksheets("newWorksheet").Cells(j, 3) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Date] " Then
                           Worksheets("newWorksheet").Cells(j, 4) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Category] " Then
                           Worksheets("newWorksheet").Cells(j, 5) = Worksheets("original").Cells(i + k + 3, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Author] " Then
                           Worksheets("newWorksheet").Cells(j, 6) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[NumPages] " Then
                           Worksheets("newWorksheet").Cells(j, 7) = Worksheets("original").Cells(i + k, 2)
                        ElseIf Worksheets("original").Cells(i + k, 1) = "[Title] " Then
                           Worksheets("newWorksheet").Cells(j, 8) = Worksheets("original").Cells(i + k, 2)
                        ' *****COMMENT  ")" is the markup signalling a new item
                          ElseIf Worksheets("original").Cells(i + k, 1) = ")" Then
                           j = j + 1
                           Exit For
                        End If
                    Next
            End If
        Next
    Next
End Sub

 

[AlphaFrog]

Try this.

[color=darkblue]Sub[/color] data()
    
   [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] Long
   [color=darkblue]Dim[/color] wsNew [color=darkblue]As[/color] Worksheet
   
   [color=darkblue]Set[/color] wsNew = Worksheets("newWorksheet")   [color=green]'Destination worksheet[/color]
   j = 2    [color=green]'New worksheet start row[/color]
   
   [color=darkblue]With[/color] Worksheets("original")  [color=green]'Source worksheet[/color]
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] .Range("A" & Rows.Count).End(xlUp).Row [color=green]'1 to last used row[/color]
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] .Cells(i, 1).Value
             [color=darkblue]Case[/color] "[id] "
                wsNew.Cells(j, 1) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Subject] "
                wsNew.Cells(j, 2) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Content] "
                wsNew.Cells(j, 3) = .Cells(i, 2)
                [color=darkblue]Do[/color] [color=darkblue]While[/color] [color=darkblue]Not[/color] IsEmpty(.Cells(i + 1, 1)) And Left(.Cells(i + 1, 1), 1) <> "["
                    i = i + 1
                    wsNew.Cells(j, 3) = wsNew.Cells(j, 3) & .Cells(i, 1)
                [color=darkblue]Loop[/color]
             [color=darkblue]Case[/color] "[Date] "
                wsNew.Cells(j, 4) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Category] "
                wsNew.Cells(j, 5) = .Cells(i + 3, 2)    [color=green]'??? why plus 3[/color]
             [color=darkblue]Case[/color] "[Author] "
                wsNew.Cells(j, 6) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[NumPages] "
                wsNew.Cells(j, 7) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Title] "
                wsNew.Cells(j, 8) = .Cells(i, 2)
             [color=darkblue]Case[/color] ")" [color=green]' markup signaling a new item[/color]
                j = j + 1
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
     [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[desarmo]

Try this.

Oh wow! Thanks so much for your help!!!! I think that is a much more efficient and elegant solution to this problem. The only thing is that in some of the instances when [content] data is in column 1, there are empty Column 1 cells. So I think these blank cells are still causing a problem. The code you shared performed beautifully for converting the data in a more standard table format with each item occupying only a single row, except it didn't concatenate the column 1 data. I think this is because of blank cells in the first column of the original sheet. For example:

[title]	book 1
[content]	this is the beginning
{THIS IS GENERALLY TOTALLY BLANK}
then more content for book 1 continues
{THEN ANOTHER BLANK CELL}
then a little more content for book 1
)
[title]	book 2
[content]	content for book 2

<tbody>
</tbody>

 

[AlphaFrog]

[color=darkblue]Sub[/color] data()
    
   [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRow [color=darkblue]As[/color] Long
   [color=darkblue]Dim[/color] wsNew [color=darkblue]As[/color] Worksheet
   
   [color=darkblue]Set[/color] wsNew = Worksheets("newWorksheet")   [color=green]'Destination worksheet[/color]
   j = 2    [color=green]'New worksheet start row[/color]
      
   [color=darkblue]With[/color] Worksheets("original")  [color=green]'Source worksheet[/color]
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow [color=green]'1 to last used row[/color]
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] .Cells(i, 1).Value
             [color=darkblue]Case[/color] "[id] "
                wsNew.Cells(j, 1) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Subject] "
                wsNew.Cells(j, 2) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Content] "
                wsNew.Cells(j, 3) = .Cells(i, 2)
                [color=darkblue]Do[/color] [color=darkblue]Until[/color] Left(.Cells(i + 1, 1), 1) = "[" [color=darkblue]Or[/color] i = LastRow
                    i = i + 1
                    wsNew.Cells(j, 3) = wsNew.Cells(j, 3) & .Cells(i, 1)
                [color=darkblue]Loop[/color]
             [color=darkblue]Case[/color] "[Date] "
                wsNew.Cells(j, 4) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Category] "
                wsNew.Cells(j, 5) = .Cells(i + 3, 2)    [color=green]'??? why plus 3[/color]
             [color=darkblue]Case[/color] "[Author] "
                wsNew.Cells(j, 6) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[NumPages] "
                wsNew.Cells(j, 7) = .Cells(i, 2)
             [color=darkblue]Case[/color] "[Title] "
                wsNew.Cells(j, 8) = .Cells(i, 2)
             [color=darkblue]Case[/color] ")" [color=green]' markup signaling a new item[/color]
                j = j + 1
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
     [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[desarmo]

WOW! This worked beautifully! And fast! My code with loops and loops and loops took relatively forever to run. The code you shared was super fast, like instant, handled the empty cells wonderfully, and did the concatenation just as was in my dreams. Your code produced the exact result I was trying so hard to achieve, and has saved me weeks of manual copy and pasting!!!!

You are a genius! Thank you so much for your help!!!!

 

[AlphaFrog]

You're welcome.