Concatenate above cells into the blank cell below

[IT990]

Hi,

I am trying to have replace the blank cell (which is essentially a subtotal cell) with a concatenation of the above cells. Unfortunately there is a random number of cells above each blank. see below:

Trying to turn this:

orange

apple

carrot

banana

pear

pineapple

lettuce

To this:

orange

apple

orange, apple

carrot

carrot

banana

pear

pineapple

lettuce

banana, pear, pineapple, lettuce

Thanks in advance

[#2]

@IT990 

To achieve the desired result in Excel, you can use a formula that concatenates the values from the above cells into the blank cell below. You can use a combination of the IF and CONCATENATE (or &) functions along with relative cell references.

Here is an example formula you can use:

1. In the cell below "orange," assuming it's cell A2, enter the following formula:

=IF(A2<>"", A2, CONCATENATE(A1, ", ", A2))

1. Drag the formula down to fill the remaining blank cells.

This formula checks if the current cell is not empty. If it is not empty, it simply returns the value from that cell. If the cell is empty, it concatenates the value from the cell above (A1) with the current cell (A2) using a comma and space separator.

By dragging the formula down, it will adjust the cell references accordingly and concatenate the appropriate values from the above cells.

The result should be as follows:

orange

apple

orange, apple

carrot

carrot

banana

pear

pineapple

lettuce

banana, pear, pineapple, lettuce

Please note that the formula assumes your data starts from cell A1.

Adjust the formula accordingly if your data starts from a different cell.

CONCATENATE

Note: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.

IF

Note: If you are going to use text in formulas, you need to wrap the text in quotes (e.g. “Text”). The only exception to that is using TRUE or FALSE, which Excel automatically understands.

[#3 · Accepted solution]

@IT990 

Select the range, including the blank cell at the end.

Then run this macro:

Sub FillTheBlanks()
    Dim r1 As Long
    Dim r2 As Long
    Application.ScreenUpdating = False
    r1 = 1
    For r2 = 2 To Selection.Rows.Count
        If Selection.Range("A" & r2).Value = "" Then
            Selection.Range("A" & r2).Value = Application.TextJoin(", ", _
                True, Selection.Range("A" & r1 & ":A" & r2))
            r1 = r2 + 1
        End If
    Next r2
    Application.ScreenUpdating = True
End Sub

[#4]

@IT990 

Another solution with VBA could be this code.

Sub concatenate()

Dim i, j As Long
Dim str As String

i = Range("A" & Rows.Count).End(xlUp).Row + 1

For j = 1 To i

If Cells(j, 1).Value <> "" Then

Cells(j, 2).Value = Cells(j, 1).Value
    
    If str = "" Then
    
    str = Cells(j, 1).Value
    
    Else
    str = str & ", " & Cells(j, 1).Value
    End If

Else

Cells(j, 2).Value = str

str = ""
End If

Next j

End Sub

[#5]

Thanks for all the responses. I used @Hans Vogelaar's one and it works perfectly.