Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel

Copper Contributor

I am trying to separate excel Data by date sequence, then by location. Is it possible to add 3 blank lines between each new date and between each location using a formula or pivot?

Anna8atovar_0-1700081198231.png

 

1 Reply

@Anna8atovarIf I've understood you correctly, you want to group your data by AppDate (column 1), then by FclId (column 6), with 3 blank rows inserted after each grouping. One possible way to do this is with a combination of dynamic array formulas as follows:

 

=LET(
   data, A2:F6,
   cols, CHOOSECOLS(data, 1, 6),
   n, COLUMNS(cols),
   k, TOROW(UNIQUE(cols)),
   keys, WRAPROWS(TOROW(IF(SEQUENCE(3), k)), n),
   arr, IFNA(VSTACK(HSTACK(cols, data), keys), ""),
   DROP(SORT(arr, SEQUENCE(, n)),, n)
)

 

Note: adjust the data range "A2:F6" to include your entire data set.

 

This is a difficult one to explain, but basically what's happening is we've created "sort fields" consisting of the original data from the selected columns, plus a unique list of values from the selected columns repeated 3 times at the bottom. The sort fields are then added to the original data range, and the new rows are filled with empty text strings (""). The resulting array is sorted by the sort fields, which are then removed from the final results.

 

Additional Notes:

  • Adjust SEQUENCE(3) to change the number of blank rows to be inserted
  • Adjust CHOOSECOLS(data, 1, 6) to change the sort index, or to select additional columns:
    • ie: CHOOSECOLS(data, 6, 1) will reverse the sort index 
    • ie: CHOOSECOLS(data, 1, 6, 5) will also group by DctId
  • The results will not be formatted automatically, so you will need to apply the appropriate Date/Time formatting to each applicable column
  • If you need the results to be returned as a set of static values that you can work with, Copy the entire array of results, then Paste Special > Values
  • The blank rows will not actually be blank, because they were filled with empty text strings ("")
    • If you need them to be blank, use a character that can easily be replaced later (using Find and Select > Replace), such as the Greek Small Letter Lambda symbol
    • ie: IFNA(VSTACK(HSTACK(cols, data), keys), "λ")