VBA - Concatenate based on values in another cell

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.

concatenate column e into row with text
for exable E1&E2 concatenate into E2

<tbody>
</tbody><colgroup><col></colgroup>

also

concatenate column e into row with text
for exable E20&E21&E22 concatenate into E21

<tbody>
</tbody><colgroup><col></colgroup>


Saved
Parker, PeterPPHero12008Dog15/4/2017Yes
Saved
Kent, ClarkCKHero12008Dog15/15/2017Yes
Saved
Gardner, GuyGGHero12008Dog15/4/2017Yes
Saved
Stark, TonyTSHero12208Cat25/5/2017Yes
Saved
Rodgers, SteveSRHero12208Cat25/15/2017Yes
Saved
Wayne, BruceBWHero12208Cat25/4/2017
Saved
Allen, BarryBAHero12408Girl15/6/2017Yes
Saved
Xavier, CharlesCXHero12408Girl15/16/2017Yes
Saved
Grey, JeanJGHero12408Family15/4/2017Yes
Of 5
Saved
Banner, BruceBBHero12708Bus15/8/2017Yes
Defeated Villian
Saved
Summers, ScottSSHero12408Family15/4/2017Yes
Of 5

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
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
 

hoodedrobin1

New Member
Joined
Jun 19, 2017
Messages
10
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

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
 

hoodedrobin1

New Member
Joined
Jun 19, 2017
Messages
10
By the way I believe your formula works.

I don't know how to edit a previous post.

-HoodedRobin1
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401

ADVERTISEMENT

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
 

hoodedrobin1

New Member
Joined
Jun 19, 2017
Messages
10
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top