How to search for cell value and concatenate to row above

[kkbakic]

I have an Excel spreadsheet with house addresses that are inconsistent. I would like to write vba code that searches for the word "UNIT" in column A and then concatenates it with the address row above. Something like:

<code>Sub ConcatenateRowAbove()

Dim aCell as Range
Dim lrow as Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

With ws</code>'find last row in column A which has data
<code>lrow = .range("A" & Rows.Count).End(xlUp).Row</code>'loop through each cell in column A and find text "UNIT" then concatenate with address in row above
<code>For Each aCell in .Range("A" & lrow)

If acell.value = "UNIT" Then _

aCell.FormulaR1C1 = "=CONCATENATE(R[-1]C[1])"

Next aCell

End With

End Sub</code>Example:
101 E Main St
UNIT 1
102 E Main St
103 E Main St
104 E Main St
UNIT 1
104 E Main St
UNIT 2

RESULTS: Column B would show as Concatenated IF there is a Unit Address:
101 E Main St, UNIT 1
102 E Main St
103 E Main St
104 E Main St, UNIT 1
104 E Main St, UNIT 2

 

[iliace]

Adapting to your code, it should be something like this:

Sub ConcatenateRowAbove()
  Dim aCell As Range
  Dim lrow As Long
  Dim ws As Worksheet
  
  Dim i As Long
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  'find last row in column A which has data
  With ws
    'loop through each cell in column A and find text "UNIT" then concatenate with address in row above
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    
    For i = lrow To 1 Step -1
      Set aCell = .Cells(i, "A")
      If Left(aCell.Value, 4) = "UNIT" Then
        aCell.Offset(-1, 0).Value = aCell.Offset(-1, 0).Value & ", " & aCell.Value
        aCell.EntireRow.Delete
      End If
    Next i
  End With

End Sub

Be sure to try this on a copy of your data first, because the results of this (whether correct or not) will be permanent/not reversible.

 

[kkbakic]

Adapting to your code, it should be something like this:

Sub ConcatenateRowAbove()
  Dim aCell As Range
  Dim lrow As Long
  Dim ws As Worksheet
  
  Dim i As Long
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  'find last row in column A which has data
  With ws
    'loop through each cell in column A and find text "UNIT" then concatenate with address in row above
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    
    For i = lrow To 1 Step -1
      Set aCell = .Cells(i, "A")
      If Left(aCell.Value, 4) = "UNIT" Then
        aCell.Offset(-1, 0).Value = aCell.Offset(-1, 0).Value & ", " & aCell.Value
        aCell.EntireRow.Delete
      End If
    Next i
  End With

End Sub

Be sure to try this on a copy of your data first, because the results of this (whether correct or not) will be permanent/not reversible.

THANKS iliace! That worked perfectly! I'm a self-taught newby - totally forgot about the offset property