Continue to increment formula by N rows across columns in Google Sheets

Question

I am trying to use UNIQUE= to grab unique values in a section of 11 rows from an XML parse. Every column I want to target the next set of rows down the column but I'm not sure how to do this automatically, dragging the formula (auto-fill) doesn't work.

Here is a link to my sheet with the UNIQUE formulas I have so far. You can see they increase by 11 each time. How can I continue that pattern without creating a new formula each time.

=UNIQUE($A2:$A13)
=UNIQUE($A14:$A25)
=UNIQUE($A26:$A37)

Here is my Google Sheet so far.

https://docs.google.com/spreadsheets/d/1y1cGi0Qy6a6PiQtQUrb5FQ99V31c_tEGi4aLvktxShE/edit?usp=sharing

Any help would be greatly appreciated!

player0 player0 · Accepted Answer · 2020-05-24T18:50:36

paste in B2 and drag to the right:

=UNIQUE(INDIRECT("A"&2+(COLUMN()-2)*11+COLUMN()-2&":A"&13+(COLUMN()-2)*11+COLUMN()-2))

Continue to increment formula by N rows across columns in Google Sheets

2 Answers