Forum Discussion
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 |
Thanks in advance
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
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:
- In the cell below "orange," assuming it's cell A2, enter the following formula:
=IF(A2<>"", A2, CONCATENATE(A1, ", ", A2))
- 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.
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.
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.
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 SubAnother 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- Thanks for all the responses. I used HansVogelaar's one and it works perfectly.
