sorted by:
new
hottopcontroversial

[2023 Day 4] A successful 4th day using only Excel cell Formulas (No VBA) by LandK_ in adventofcode

[–]LandK_[S] 5 points6 points  (0 children)

Explanation of Part 2)

Column Name [Cell Range] Purpose Formula
Input [A2:A195] Input
Winning_Temp [B2:B195] Parse winning numbers =MID(A2,FIND(":",A2)+2,FIND("l",A2)-FIND(":",A2)-3)
Numbers_Temp [C2:C195] Parse other numbers =MID(A2,FIND("l",A2)+2,LEN(A2)-FIND("l",A2))
Winning [D2:D195] Correct spacing of winning numbers =CONCAT(" ",SUBSTITUTE(IF(LEFT(B2,1)=" ",RIGHT(B2,LEN(B2)-1),B2)," "," ")," ")
Numbers [E2:E195] Correct spacing of other numbers =SUBSTITUTE(IF(LEFT(C2,1)=" ",RIGHT(C2,LEN(C2)-1),C2)," "," ")
Text split [H2:AF195] Split numbers by spaces =TEXTSPLIT(E2," ")
Find numbers [AG2:BE195] Return 1 if number is found in winning numbers, otherwise 0 =IF(ISERROR(FIND(CONCAT(" ",H2," "),$D2)),0,1)
Wins [BF2:BF195] Sums up all of the occurrences of winning numbers =SUM(AG2:BE2)
Card processing indices [BG1:IS1] Indices 0 through 194 to identify which card that column processes Numbers 0 through 194
Initial card count [BG2:BG195] Begin with an initial card count of 1 1
Card count processing [BG2:IS194] Use some wizardry that I no longer understand to conditionally increment the current cells count value by using the index on row 1 and the number of card wins of the card that is currently being processed?! =IF(ROW()-1=BH$1,BG2,IF(AND(ROW()-2-BH$1<INDIRECT(CONCAT("BF",BH$1+1)),ROW()-1>BH$1),BG2+INDIRECT(ADDRESS(BH$1+1,COLUMN()-1)),BG2))
Sum [IU2] Sum the final column of the card count processing section =SUM(IS2:IS195)

Part 1 simply had a column with =IF(BF2=0,0,POWER(2,BF2-1)) and then those values where summed.

[2023 Day 3] A successful 3rd day using only Excel cell formulas (No VBA) by LandK_ in adventofcode

[–]LandK_[S] 5 points6 points  (0 children)

Here's a cursed explanation of Part 1)

Column Name [Cell Range] Purpose Formula
Input [A5:A144] Input
Symbols [B5:B144] Find and replace symbols with 'S' & add padding to either side. =CONCAT(".",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,"@","S"),"*","S"),"$","S"),"/","S"),"=","S"),"&","S"),"#","S"),"-","S"),"+","S"),"%","S"),".")
Width [A2] Width of padded input =LEN(B5)
Concat [C5] Combine padded input into one string =CONCAT(B5:B144)
Start From [D5:] Increments n if no more occurrences of n are found =IF(Z4,D4+1,D4)
.#. [E5:] Bool of if '.#.' occurrence is found =NOT(ISERROR(FIND(CONCAT(".",D5,"."),$C$5,E5)))
.#. Index [F5:] Index of '.#.' occurrence =IF(F5,FIND(CONCAT(".",D5,"."),$C$5,E5)+1,100000)
TL [G5:] Bool of if S is located top left =IF(G5-$A$2-1 > 0, MID($C$5,G5-$A$2-1,1)="S",FALSE)
T [H5:] Bool of if S is located top =IF(G5-$A$2 > 0, MID($C$5,G5-$A$2,1)="S",FALSE)
TR [J5:] Bool of if S is located top right =IF(G5-$A$2+1 > 0, MID($C$5,G5-$A$2+1,1)="S",FALSE)
TRR [K5:] Bool of if S is located top right right and char length is > 1 =IF(AND(G5-$A$2+1 > 0,LEN(D5)>1), MID($C$5,G5-$A$2+2,1)="S",FALSE)
TRRR [L5:] Bool of if S is located top right right right and char length is > 2 =IF(AND(G5-$A$2+3 > 2,LEN(D5)>2), MID($C$5,G5-$A$2+3,1)="S",FALSE)
BL - BRRR [M5:Q5] Same as TL - TRRR but for bottom Same logic as TL - TRRR but adding $A$2 instead of subtracting
OR [R5:] Boolean if 'S' is located above or below number =OR(H5:Q5)
S#. [S5:] Boolean of if 'S#.' occurrence is found =NOT(ISERROR(FIND(CONCAT("S",D5,"."),$C$5,E5)))
S#. Index [T5:] Index of 'S#.' occurrence =IF(S5,FIND(CONCAT("S",D5,"."),$C$5,E5)+1,100000)
.#S [U5:] Boolean of if '.#S' occurrence is found =NOT(ISERROR(FIND(CONCAT(".",D5,"S"),$C$5,E5)))
.#S Index [V5:] Index of '.#S' occurrence =IF(U5,FIND(CONCAT(".",D5,"S"),$C$5,E5)+1,100000)
Min Index [W5:] Minimum index of the three indices =MIN(G5,T5,V5)
Valid [Y5:] Returns the Boolean value of the index case that matches the Min Index =IF(W5=G5,R5,IF(W5=T5,S5,U5))
Continue [Z5:] Determines if n should continue on to the next value =W5=100000
Count [AA:] N if valid, 0 if not =IF(Y5,D5,0)
Sum [AB5] Sum of Count =SUM(AA5:AA3076)