May 26 2023 02:37 AM
May 26 2023 02:37 AM
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:
|banana, pear, pineapple, lettuce|
Thanks in advance
May 26 2023 02:54 AM
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:
=IF(A2<>"", A2, CONCATENATE(A1, ", ", A2))
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:
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.
May 26 2023 02:55 AMSolution
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
May 26 2023 04:04 AM
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