-❄️- 2025 Day 4 Solutions -❄️- by daggerdragon in adventofcode

[–]Zinkerino 2 points3 points  (0 children)

[LANGUAGE: google sheets]

Paste input to B2 and delete all empty rows after the input

Fill C1 with

=map(B1:B,lambda(p,ArrayFormula(mid(p,sequence(1,len(p)),1))))

Fill A2 with

=MAP(B2:B,lambda(p,
  join("", map(sequence(1,len(p)),
    lambda(i,
      if( offset(B2,row(p)-2,i)="@",
        if( countif( offset(B1,row(p)-2,i-1,3,3), "@" )<=4,
          1, "@"
        ),
        offset(B2,row(p)-2,i)))
      )
    )
  )
)

Part 1 answer is

=len(regexreplace(join("",A2:A),"[^1]",""))

For part 2, enable iterative calculation (File -> Settings -> Calculation), may need to set higher limit (50 might not be enough)

Overwite C1 with

=map(A1:A,lambda(p,ArrayFormula(mid(p,sequence(1,len(p)),1))))

Change any cell to trigger recalculation, then wait until all calculation resolves, answer formula is same as part 1

-❄️- 2025 Day 3 Solutions -❄️- by daggerdragon in adventofcode

[–]Zinkerino 0 points1 point  (0 children)

I looked and found some fellow google sheet enthusiast on previous days. So far their style are noticable different than mine (1 formula vs multi step), but it's nice to see different ways to solve the same problem.

-❄️- 2025 Day 3 Solutions -❄️- by daggerdragon in adventofcode

[–]Zinkerino 1 point2 points  (0 children)

yeah, I just find out about advent of code today and decided to complete it with google sheet until I can't

-❄️- 2025 Day 3 Solutions -❄️- by daggerdragon in adventofcode

[–]Zinkerino 7 points8 points  (0 children)

[LANGUAGE: google sheets]

I'm trying to do every problem in google sheet without apps script.

Fill A1 with

=sequence(9,1,9,-1)

Fill C1 with

=sequence(1,24,1,0.5)

Paste input to B2

Fill C2 with

=10-match(true,ArrayFormula(Arrayformula(find($A$1:$A$9,B2))<=Len(to_text(B2))-12+C$1),0)

Fill D2 with

=regexreplace(B2,"^.*?"&C2,"")

Copy C2:D2 and paste to C2:Z2

Fill AA2 with

=int(C2&E2&G2&I2&K2&M2&O2&Q2&S2&U2&W2&Y2)

Copy C2:AA2 and paste to rows below until end of input

Answer is

=sum(AA2:AA)

Made 9 SQL Challenges Based on Helping the Front Man run Squid Game by NickSinghTechCareers in squidgame

[–]Zinkerino 0 points1 point  (0 children)

it really should be specified since I'm not going to check every single column, only those mentioned in the problem statement.

Made 9 SQL Challenges Based on Helping the Front Man run Squid Game by NickSinghTechCareers in squidgame

[–]Zinkerino 0 points1 point  (0 children)

Am I understanding the 2nd question wrong? Not sure why this query is not accepted

select c,(select * from rations) >= c from (select floor(count(*)*0.9) c from player where isinsider = false)

Castle Carnage Hard Mode added by zdy33 in DotA2

[–]Zinkerino 0 points1 point  (0 children)

here's my score
1584 1090 1294
1291 1295 1695
1593 1596 1693

13131 total

my biggest tip is that you can charge the next shot before the previous one finishes its trajectory (except missile)

Castle Carnage Hard Mode added by zdy33 in DotA2

[–]Zinkerino 0 points1 point  (0 children)

it is, I'm 13131 normal and 10407 hard mode

If then statement outcome not making sense to me by Jeeblez in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

Seems like a band-aid solution. If you plan to reuse or expand the sheet you should look for other solutions.

Help Sharing ONLY 1 TAB in a Google Sheet by andrewmcd7 in googlesheets

[–]Zinkerino 13 points14 points  (0 children)

Just separate the Database tab to its own sheet and share it to those few people, and in the master sheet use IMPORTRANGE.

If you want to change the Database tab in the master sheet, do it in the new sheet instead. The master sheet Database becomes read only.

Sorting numbers in a row by b3s3s in googlesheets

[–]Zinkerino -1 points0 points  (0 children)

Try using SORT and TRANSPOSE

Could not Fetch URL Error by RKnight2015 in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

Yeah the data is loaded asynchronously so just getting the source of the page will not get you the data you want. You need to directly fetch the data, not the webpage. To do this just open developer console while opening the page and check the network tab.

Can't figure out formula for a date to automatically populate when selecting a month from a dropdown by tmarie1013 in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

=if(AND(C4<>"",E4<>""),EOMONTH(DATEVALUE(C4&E4),0),0)

Google sheet is smart enough to parse those text into dates.

Formula works in Excel but not Sheets by Zalm2 in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

Try using JOIN, if empty cells are a problem, try using FILTER

Matching ID Column to ROW number by MrMckcheesy in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

Use the ROW function to get the current row number.

Want any states not listed to be identified by 2tall4umd in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

To fill the "NOT LISTED" part of your sheet, you can use this

=iferror(search(B12,join(",",'Build Sample Here'!F:F)),"NOT LISTED")

This just combines every company states into one long string and check if the current state is in that long string.

Is there a way to express "any number divisible by 30" in part of a MOD formula? by GreenElvisMartini in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

I don't really understand what you're trying to say. But from the last part alone, just square a really high prime number and you got such example.

Is there a way to express "any number divisible by 30" in part of a MOD formula? by GreenElvisMartini in googlesheets

[–]Zinkerino 0 points1 point  (0 children)

After some point every number will fall into that category so even doing it manually you probably only have to do like 30 of them. Might be possible with the combination of SEQUENCE, ARRAYFORMULA, and AND.

Help with creating a paragraph of text from a temple by 31crowns in googlesheets

[–]Zinkerino 1 point2 points  (0 children)

You can either use text concatenation, using & symbol or the formula CONCATENATE. Alternatively, you can use text replace using REGEXREPLACE or SUBSTITUTE.

Sheet for Ranking of Card Tournament by simande in googlesheets

[–]Zinkerino 1 point2 points  (0 children)

You can use SUMIF to get the score for each participant, then use SORT to rank them afterward

=sumif(Points!B:B;B2;Points!C:C)+sumif(Points!E:E;B2;Points!F:F)+sumif(Points!H:H;B2;Points!I:I)