SOLVED

# Concatenate above cells into the blank cell below

Copper Contributor

# Concatenate above cells into the blank cell below

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

4 Replies

# Re: Concatenate above cells into the blank cell below

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.

best response confirmed by IT990 (Copper Contributor)
Solution

# Re: Concatenate above cells into the blank cell below

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``````

# Re: Concatenate above cells into the blank cell below

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``````

# Re: Concatenate above cells into the blank cell below

Thanks for all the responses. I used @HansVogelaar's one and it works perfectly.
1 best response

Accepted Solutions
best response confirmed by IT990 (Copper Contributor)
Solution

# Re: Concatenate above cells into the blank cell below

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``````