CODEWITHSUNDEEP
arraysgoogle-sheetsuniquegoogle-sheets-formula
Bricky
Bricky

Reputation: 3

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

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!

Upvotes: 0

Views: 721

Answers (2)

MattKing
MattKing

Reputation: 7783

It looks to me like the whole thing can be done in one formula. Try creating a sheet from scratch and putting this in cell A2...

=ARRAYFORMULA(HLOOKUP("X",{"X";IMPORTXML("https://www67.myfantasyleague.com/2020/export?TYPE=schedule&L=14002&APIKEY=&W=1&F=&JSON=0","//matchup/franchise/@id")},SEQUENCE(1,14,0,12)+SEQUENCE(12)+1))

Upvotes: 0

player0
player0

Reputation: 1

paste in B2 and drag to the right:

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

Upvotes: 1

Related Questions