VBA - Concatenate based on values in another cell

H

hoodedrobin1

New Member
Joined
Jun 19, 2017
Messages
10
I have some advance concatenate questions I am having a problem solving.

I have some data that is incorrectly broken into separate rows when scraping data from a website. I want to be able to combine the rows in question into the same row as text found on the left of said column.

Hopefully below someone can see how I need to do. The fifth column (E) is not in one cell and needs to be grouped based on text to the left.

[TABLE="width: 514"]
<tbody>[TR]
[TD]concatenate column e into row with text
for exable E1&E2 concatenate into E2[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

also

[TABLE="width: 514"]
<tbody>[TR]
[TD]concatenate column e into row with text
for exable E20&E21&E22 concatenate into E21[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


[TABLE="width: 534"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parker, Peter[/TD]
[TD]PP[/TD]
[TD]Hero[/TD]
[TD]12008[/TD]
[TD]Dog[/TD]
[TD]1[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kent, Clark[/TD]
[TD]CK[/TD]
[TD]Hero[/TD]
[TD]12008[/TD]
[TD]Dog[/TD]
[TD]1[/TD]
[TD="align: right"]5/15/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gardner, Guy[/TD]
[TD]GG[/TD]
[TD]Hero[/TD]
[TD]12008[/TD]
[TD]Dog[/TD]
[TD]1[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stark, Tony[/TD]
[TD]TS[/TD]
[TD]Hero[/TD]
[TD]12208[/TD]
[TD]Cat[/TD]
[TD]2[/TD]
[TD="align: right"]5/5/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rodgers, Steve[/TD]
[TD]SR[/TD]
[TD]Hero[/TD]
[TD]12208[/TD]
[TD]Cat[/TD]
[TD]2[/TD]
[TD="align: right"]5/15/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wayne, Bruce[/TD]
[TD]BW[/TD]
[TD]Hero[/TD]
[TD]12208[/TD]
[TD]Cat[/TD]
[TD]2[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Allen, Barry[/TD]
[TD]BA[/TD]
[TD]Hero[/TD]
[TD]12408[/TD]
[TD]Girl[/TD]
[TD]1[/TD]
[TD="align: right"]5/6/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Xavier, Charles[/TD]
[TD]CX[/TD]
[TD]Hero[/TD]
[TD]12408[/TD]
[TD]Girl[/TD]
[TD]1[/TD]
[TD="align: right"]5/16/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grey, Jean[/TD]
[TD]JG[/TD]
[TD]Hero[/TD]
[TD]12408[/TD]
[TD]Family[/TD]
[TD]1[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Of 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Banner, Bruce[/TD]
[TD]BB[/TD]
[TD]Hero[/TD]
[TD]12708[/TD]
[TD]Bus[/TD]
[TD]1[/TD]
[TD="align: right"]5/8/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Defeated Villian[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Saved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summers, Scott[/TD]
[TD]SS[/TD]
[TD]Hero[/TD]
[TD]12408[/TD]
[TD]Family[/TD]
[TD]1[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Of 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
Click to expand...
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
I can't be 100% certain that this is right because I'm not clear on whether the blank lines belong to the cell above or below them but this does what you said in your description for the data posted:

Code: 
Public Sub ConcatenateColumnE()

Dim lastRow As Long
Dim thisRow As Long
Dim currentValue As String

' Find the last row with an entry in column E
lastRow = Cells(Rows.Count, 5).End(xlUp).Row

' Start on the first row
thisRow = 1

' Keep going until the end
Do While thisRow <= lastRow
    ' Does this row have an empty cell in column A?
    If Trim$(Cells(thisRow, 1).Value) = "" Then
        ' Remember the value from column E and remove the row
        currentValue = currentValue & Cells(thisRow, 5).Value & vbCrLf
        Rows(thisRow).Delete xlShiftUp
        
        ' Since we've removed a row, the last row has moved up one
        lastRow = lastRow - 1
    Else
        ' Enter the remembered values from column E
        Cells(thisRow, 5).Value = currentValue & Cells(thisRow, 5).Value
        currentValue = ""
        
        ' Process the next row
        thisRow = thisRow + 1
    End If
Loop

End Sub

WBD
 
Upvote 0
wideboydixon said:
I can't be 100% certain that this is right because I'm not clear on whether the blank lines belong to the cell above or below them but this does what you said in your description for the data posted:
WBD
Click to expand...

Wideboydixon, I color coded them. Some lines have 2 rows of data in column E some have 3 or 4.

At the bottom you can see that there are 2 blanks above Banner, Bruce but only "Saved, Bus, Defeated Villain" is related, not "Of 5"

-HoodedRobin1
 
Upvote 0
Ahh. Now the colours make sense! What's the logic for deciding which row the extra cells belong to? I assume your sheet is not colour-coded.

WBD
 
Upvote 0
I actually set my test sheet up incorrectly... (I had to change every field because it was proprietary)

When I ran your code, it was legible and made sense (on my real data)



So thank you again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Export excel file as PDF into a targeted pc folder based on cell value
Replies
2
Views
197
Ramadan2512
R
M
  • Question Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
410
Cubist
Cubist
S
  • Question Question
VBA - Code for comparing two sheets and extracting rows based on unique column value
Replies
1
Views
500
Starbel
S
L
  • Question Question
VBA Code to paste special values over a cell depending on another cells value....
Replies
1
Views
2K
Micron
M
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top