google sheets
I'm trying to write a simple formula that concatenates each entry in a given column of a spreadsheet, interspersed with newline characters. In other words,
=CONCATENATE(B2, CHAR(10), B3, CHAR(10), B4, CHAR(10), ... , CHAR(10), B130)
Is there any way of achieve this without having to type the whole thing out by hand? Even copying and pasting so I just have to modify the cell references is pretty laborious. I was thinking that there might be some way of implementing a for loop?
WOW, I was going to get you an exact solution, Google Docs is so frustrating. Script-loop City.
You can do this with the FILTER worksheet function, with your arrayCondition_1 being a formula of functions that gives a true value for each line that isn't duped. I was able to see that the MATCH function couldn't be used.
Maybe this has been fixed in New Google Sheets because it works for me (indeed, it is VERY simple):
Select A1:B3, Insert, Chart..., click Line chart, Insert.
Seems happy to work with DMY format and, as it happens, text for dates (since happening to be 4 months apart the x-axis spacing to be equal is about correct anyway).
Best Answer
You need the
JOINfunction. This function joins all the elements in an array (ie. a 1-dimensional range, a single column or a single row) separated by a common delimiter (yourCHAR(10)).I believe the
CONCATENATEfunction has a limit of 30 arguments, so that could also have been a problem.