Insert blank lines

Question

Hello,

One of our analysts has a weekly chore to organize a worksheet with several thousand rows. Column A has dates that are sorted in ascending order. There will be multiple rows with the same date, Her choir is to insert a blank row at each change of date.

Is there an easy way to do this?

Thanks for any suggestions.

Answer 1

Hi,

In cell C2, enter this formula

=DROP(REDUCE(A2,DROP(A2:A13,1),LAMBDA(a,b,VSTACK(a,IF(b<>OFFSET(b,1,0),EXPAND(b,2,,""),b)))),-1)

Hope this helps.

Answer 2

Hi,

The following formula avoids using the OFFSET function, which is a volatile function. Try this formula and let me know if it works or not.

=LET(d, SORT(TOCOL(A2:A10000, 3)),at, ARRAYTOTEXT(BYROW(UNIQUE(d), LAMBDA(a, TEXTJOIN(",",, FILTER(d,d = a))))&",", 0), IFERROR(--TEXTSPLIT(MID(at, 1, LEN(at) -1),, ","), ""))

Hope this helps.

Answer 3

You can use subtotals to do this.

Select all your data:

(Here, you can see that I bolded similar dates.)

Go to the Data tab, the Outline section and choose Subtotal:

In the subsequent dialogue box choose similar to the following. Don't add the subtotal to the first column (Date in this case), it'll add a column.

Click OK and you'll get the likes of:

Where you'll see you also get an outline added on the left which you may not want. You can get rid of it by Ungroup|Clear Outline:

leaving you with:

Now to convert all those Count lines to blank rows:

Filter the selected range using:

and filter the appropriate column for the presence of the word Count. A quick way to do this is to type in count in the search box of the filter which will select for you all those rows:

and click OK, leaving:

Note that above I've changed the selection so that the headers are NOT included, because now we're going to press the Delete key on the keyboard.

Then we remove the filter by again clicking on the filter icon:

leaving you with the desired spaces: