Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I'm completely lost and don't know how to paste any data without affecting those collapsed rows.
For example, I have such a list:
And I want to paste numbers from 1 to 4 for every capital letter into the second column. I'm collapsing all grouped letters:
But when I select the second column and paste the copied array of numbers, it overwrites all cells, including the collapsed ones. Like this:
Can anybody please explain how can I paste data without overwriting the cells from hidden rows?
Not really possible to prevent this from happening in Google Sheets, but there are always ways with formulas...
=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(COUNTIFS(IFERROR(REGEXREPLACE(
REGEXEXTRACT(A1:A, "^[A-Z]"), "[A-Z]", "A")), IFERROR(REGEXREPLACE(
REGEXEXTRACT(A1:A, "^[A-Z]"), "[A-Z]", "A")), ROW(A1:A), "<="&ROW(A1:A))*
COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A))), "^0", ""))
