AOC Day 8 2021
This is part of a greater series about solving advent of code with spreadsheets.
Part One AND Part Two
As always, read the problem text first.
In summary, this problem requires a process to unscramble segments and numbers. Each line of input contains two pieces of data …
- The 7-segment display representation of all ten digits in a random order.
- Four of any of these digits. The number these digits build is a desired output. Part one asks for the count of specific numbers. Part two asks for the total sum.
Solving Part One AND Part Two
The first part is regular ole parsing.
| DATA | A2 |
|---|---|
| DIGITS | B2,C2=SPLIT(A2,“|”) |
| NUMBER | ↑ SPILLOVER ↑ |
| DIGIT0 | D2,E2,...,M2=SPLIT(B2," ") |
| DIGIT0 | ↑ SPILLOVER ↑ |
| DIGIT0 | ↑ SPILLOVER ↑ |
| ... | ... |
| DIGIT9 | ↑ SPILLOVER ↑ |
The length of each digit is a good hint of what it is.
| LENGTH 0 | N2=LEN(D2) |
|---|---|
| LENGTH 1 | ↑ DRAG ↑ |
| ... | ... |
| LENGTH 9 | ↑ DRAG ↑ |
For example, the length cracks digit 1, 4, 7 and 8.
| 1 | X2=INDEX(D2:M2, 1, MATCH(2, N2:W2, 0)) |
|---|---|
| 4 | Y2=INDEX(D2:M2, 1, MATCH(3, N2:W2, 0)) |
| 7 | Z2=same, match for 4 segments |
| 8 | AA2=match for 7 |
Future logic needs the segments used to display 1 and 7. This trick by StackExchange’s user0 splits that string into individual characters.
| 7 SEG 0 | AB2,AC2,AD2= REGEXEXTRACT(Y2, REPT("(.)", LEN(Y2))) |
|---|---|
| 7 SEG 1 | ↑ SPILLOVER ↑ |
| 7 SEG 2 | ↑ SPILLOVER ↑ |
| C or F 00 | AE2,AF2= REGEXEXTRACT(X2, REPT("(.)", LEN(X2))) |
| C or F 1 | ↑ SPILLOVER ↑ |
(0) C or F refers to the segments originally mapped out in the problem text.
aaaa
b c
b c
dddd
e f
e f
gggg
A is the one segment that distinguishes 7 from 1. A is recoverable with StackExchange’s marc meyers set operation trick.
| A | AG2=FILTER(AB2:AD2, ISERROR(MATCH(AB2:AD2, AE2:AF2, false))) |
|---|
Onto breaking apart 4. B and D are segments that distinguish 4 and 1. The same set difference trick is reusable.
| 4 SEG 0 | AH2,AI2,AJ2,AK2= REGEXEXTRACT(Z2, REPT("(.)", LEN(Z2))) |
|---|---|
| 4 SEG 1 | ↑ SPILLOVER ↑ |
| 4 SEG 2 | ↑ SPILLOVER ↑ |
| 4 SEG 3 | ↑ SPILLOVER ↑ |
| B or D 0 | AL2,AM2=FILTER(AH2:AK2, ISERROR(MATCH(AH2:AK2, AE2:AF2, false))) |
| B or D 1 | ↑ SPILLOVER ↑ |
Next, recovering 0, 6 and 9. These three digits are the only to use 6 segments.
| 6 LEN WORD 0 | AN2,AO2,AP2= FILTER(D2:M2, N2:W2 = 6) |
|---|---|
| 6 LEN WORD 1 | ↑ SPILLOVER ↑ |
| 6 LEN WORD 2 | ↑ SPILLOVER ↑ |
While these three digits use F, 6 lacks C. Searching for these two returns two hits on 0 or 9, and one if it is 6.
| W0 CF0 INDEX | AQ2=FIND(AE2, AN2) |
|---|---|
| W0 CF1 INDEX | ↑ DRAG ↑ |
| W1 CF0 INDEX | AS2=FIND(AE2, AO2) |
| W1 CF1 INDEX | ↑ DRAG ↑ |
| W2 CF0 INDEX | AU2=FIND(AE2, AP2) |
| W2 CF1 INDEX | ↑ DRAG ↑ |
FIND will error if the substring isn’t found. This error shows which digit is 6. This check also reveals which segment is the missing C. F is then the other segment.
| 6 | AW2=IFS( ISERROR(AQ2+AR21),AN2, ISERROR(AS2+AT2),AO2, ISERROR(AU2+AV2),AP2 ) |
|---|---|
| C | AX2,AY2=IF(ISERROR(AQ2 + AS2 +
AU2), {AE2,AF22}, {AF2,AE2}) |
| F | ↑ SPILLOVER ↑ |
(1) If either AQ2 or AR2 are
errors then their addition will be an error. This is more
concise than using an OR and two
ISERRORs.
(2) This is another new trick. Formulas can return arrays that spillover.
The same kind of test works to figure out 0, this digit lacks D.
| W0 BD0 INDEX | AZ2=FIND(AL2, AN2) |
|---|---|
| W0 BD1 INDEX | ↑ DRAG ↑ |
| W1 BD0 INDEX | BB2=FIND(AL2, AO2) |
| W1 BD1 INDEX | ↑ DRAG ↑ |
| W2 BD0 INDEX | BD2=FIND(AL2, AP2) |
| W2 BD1 INDEX | ↑ DRAG ↑ |
With 0 and 6 figured out, 9 is the only remaining digit.
| 0 | BF2=IFS( ISERROR(AZ2+BA2),AN2, ISERROR(BB2+BC2),AO2, ISERROR(BD2+BE2),AP2 ) |
|---|---|
| D | BG2=IF(ISERROR(AZ2 + BB2 +
BD2), {AL2,AM2}, {AM2,AL2}) |
| B | ↑ DRAG ↑ |
| 9 | BI2=FILTER(AN2:AP2, NOT(AN2:AP2 = AW2), NOT(AN2:AP2 = BF2)) |
There are three digits left to decipher: 5, 3, and 2. Each digit needs five segments for display.
| 5 LEN WORD 0 | BJ2,BK2,BL2= FILTER(D2:M2, N2:W2 = 5) |
|---|---|
| 5 LEN WORD 1 | ↑ SPILLOVER ↑ |
| 5 LEN WORD 2 | ↑ SPILLOVER ↑ |
5 is the lone digit of this set using segment B. Additionally, 5 uses one unknown: G. To know 5 is to then know G.
| 5 | BM2=FILTER(BJ2:BL2, IFERROR(FIND(BH2, BJ2:BL2), false)) |
|---|---|
| 5 SEG 0 | BN2,BM2,BO2,BP2,BQ2,BR2= REGEXEXTRACT(BM2, REPT("(.)", LEN(BM2))) |
| 5 SEG 1 | ↑ SPILLOVER ↑ |
| ... | ... |
| 5 SEG 4 | ↑ SPILLOVER ↑ |
| G | BS2=FILTER(BN2:BR2, NOT(BN2:BR2 = AG2), NOT(BN2:BR2 = BH2), NOT(BN2:BR2 = BG2), NOT(BN2:BR2 = AY2)) |
There is one last unknown, E. The set difference comes in handy again.
| E | BT2=FILTER({"a","b","c","d","e","f","g"}, ISERROR(MATCH({"a","b","c","d","e","f","g"}, {AG2, AX2, AY2, BG2, BH2, BS2}, false))) |
|---|
3 is the last of this group to use F. The last digit then must be 2.
| 3 | BU2=FILTER(BJ2:BL2, IFERROR(FIND(AY2,BJ2:BL2), FALSE), NOT(BJ2:BL2 = BM2)) |
|---|---|
| 2 | BV2=FILTER(BJ2:BL2, NOT(BJ2:BL2 = BU2), NOT(BJ2:BL2 = BM2)) |
Phew! The last few columns are too much to include. Each formula breaks apart numbers into characters, sorts, and then reassembles. This helps match unscrambled numbers and input.
You may be interested in the previous or next day's solution.