I have 2 sheets:
Sheet2 has a simple list of strings in column A, some of which repeat, and Sheet1 displays the unique strings from Sheet2 in its own column A.
The formula to build the list in Sheet1 is =UNIQUE(filter(Sheet2!A1:A,len(Sheet2!A1:A)))
In theory... if Sheet1!A1 contains the formula in cell A1, I should be able to delete ALL rows except row 1, and the sheet should automatically expand the sheet again with the additional rows required to match the length of the filtered result.
For some reason, its adding 500 rows extra! so my filtered page gets very long and is messing with my project, where I have the same issue all over the place.
Oddly - if I then delete the 500 extra rows, they are not created again. Its only if I delete all rows except the first row containing the formula that the 500 rows appear.
Here is the simple test case sheet:
Any insight into fixing this would help me dramatically, thank you.
CodePudding user response：
The default behavior of Google Sheets is to add blocks of 500 rows at a time whenever the required output range exceeds the current available range for that output. So if your required output range is two rows and you delete (i.e. remove) all but one row in the output range, the sheet will then add 500 rows to the total required (502 rows total in this example), in anticipation that more will be added and considering that 500 more is "enough" without having any huge negative effect on processing.
If you don't always want there to be no additional rows (i.e., only enough rows to fill with necessary data), you'll need to write and apply an On-Edit script that will auto-delete those unwanted 500 rows any time they are added (by counting total rows after that happens, subtracting the number of occupied rows, moving to the end of the occupied range and deleting the difference).
There is no resident setting that would allow you to select not to have Google Sheets implement the default addition of 500 rows.
Perhaps if you explain why you need the output sheet ("Sheet2") to have no unoccupied rows, other volunteer contributors will have additional solutions to offer.