Advent of Code 2015 - Day 5: Doesn't He Have Intern-Elves For This?

Part 1

Description

Solution

So, there are three conditions to be met for a string to be nice. Therefore, in my Excel file, after putting the input in the leftmost column, I put these condition in the adjacent columns.

Column C is for counting the number of vowels, using this formula:

C3: =LEN(A3)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"a",""),"e",""),"i",""),"o",""),"u",""))

Column D is for counting any double letter, using this array formula:

D3: =SUM(IF(MID($A3,(ROW(INDIRECT("1:"&LEN($A3)))),1)=MID($A3,1+(ROW(INDIRECT("1:"&LEN($A3)))),1),1,0))

Column E is for counting any special condition above, using this formula:

E3: =LEN(A3)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"ab",""),"cd",""),"pq",""),"xy",""))

Now, what is left is counting how many strings fulfill these conditions.

Part 2

Description

Solution

For the part two, the conditions have changed. So I just added two more columns. Column H is for counting instances of two letters appear twice, using array formula:

H3: =SUM(LEN(A3)-LEN(SUBSTITUTE($A3,MID($A3,(ROW(INDIRECT("1:"&LEN($A3)-1))),2),"")))

If the result is 30, it means there is no instance of two letters appear twice. Each string in the input is exactly 16 character long. So, there will be 15 pairings. When one of these pairs (for example, xy) matches with itself, the formula will return a value of two (and total of 30 for the whole string). When it matches another pair, the formula will return another value of two. So, when the result is more than 30, it means that string contains two letters that appear more than once.

Column I is for counting if there is any letter which repeats with exactly one letter between them, using array formula:

I3: =SUM(IF(MID($A3,(ROW(INDIRECT("1:"&LEN($A3)))),1)=MID($A3,2+(ROW(INDIRECT("1:"&LEN($A3)))),1),1,0))

And again, all I need is counting how many strings fulfill these conditions.