Advent of Code 2015 - Day 7: Some Assembly Required

Part 1

Description

This year, Santa brought little Bobby Tables a set of wires and bitwise logic gates! Unfortunately, little Bobby is a little under the recommended age range, and he needs help assembling the circuit.
Each wire has an identifier (some lowercase letters) and can carry a 16-bit signal (a number from 0 to 65535). A signal is provided to each wire by a gate, another wire, or some specific value. Each wire can only get a signal from one source, but can provide its signal to multiple destinations. A gate provides no signal until all of its inputs have a signal.
The included instructions booklet describes how to connect the parts together: x AND y -> z means to connect wires x and y to an AND gate, and then connect its output to wire z.
For example:

• 123 -> x means that the signal 123 is provided to wire x.
• x AND y -> z means that the bitwise AND of wire x and wire y is provided to wire z.
• p LSHIFT 2 -> q means that the value from wire p is left-shifted by 2 and then provided to wire q.
• NOT e -> f means that the bitwise complement of the value from wire e is provided to wire f.

Other possible gates include OR (bitwise OR) and RSHIFT (right-shift). If, for some reason, you'd like to emulate the circuit instead, almost all programming languages (for example, C, JavaScript, or Python) provide operators for these gates.
For example, here is a simple circuit:

123 -> x
456 -> y
x AND y -> d
x OR y -> e
x LSHIFT 2 -> f
y RSHIFT 2 -> g
NOT x -> h
NOT y -> i

After it is run, these are the signals on the wires:

d: 72
e: 507
f: 492
g: 114
h: 65412
i: 65079
x: 123
y: 456

In little Bobby's kit's instructions booklet (provided as your puzzle input), what signal is ultimately provided to wire a?

Input

Solution

Today's problem is quite difficult for me. But it only made me more challenged. At first, I was thinking of using VBA code to automate the whole things. But, running the code takes forever, maybe because I don't know how to break the loop. But, then I realize, that this puzzle can be done also with matching and sorting. So, in my Excel file below, I split the input into 5 parts: two variables, one operator, the -> and variable 3 (the result).

The column G is for sorting those five columns. First I put this formula in the column (row 1 to 342, respectively):

=SUM(IF(B4="",1,0),IF(C4="",1,0),ISNUMBER(D4)))

This formula basically checks if the first variable and the operator is empty and the second is a number. Those meeting these conditions (signal in variable 3 is provided by variable 2) will give the sum of three. By sorting the columns (B-G) with value of G from largest to smallest, I can put those with numeric variable 2 in the top row. Then, I put this array formula in G6 (which has value less than 2):

=SUM(IF($B6="",1,0),IF($D6=$F$4:INDIRECT(ADDRESS(ROW(G$5),6)),1,0))+SUM(IF(ISNUMBER($B6),1,0),IF($D6=$F$4:INDIRECT(ADDRESS(ROW(G$5),6)),1,0))+SUM(IF(ISNUMBER($D6),1,0),IF($B6=$F$4:INDIRECT(ADDRESS(ROW(G$5),6)),1,0))

The formula again checks if any variable 1 and variable 2 matches those already declared variable 3 (c and b respectively). Only if both match, or one variable match and another one is a number, that it will give values of one (true). Again, by sorting the columns (B-G) with value of G from largest to smallest, I can put those meeting the condition in the cells below the already declared variables. I repeat the process (with the help of a Macro) until the last row (G342). And, as I already guess, there is the desired output (signal provided to a) in the last row.

Now, to give value to variables 3, I made a custom function, called Operate:

Function Operate(a, b, c) As Double
If c = "AND" Then
    Operate = WorksheetFunction.Bitand(a, b)
ElseIf c = "OR" Then
    Operate = WorksheetFunction.Bitor(a, b)
ElseIf c = "LSHIFT" Then
    Operate = WorksheetFunction.Bitlshift(a, b)
ElseIf c = "RSHIFT" Then
    Operate = WorksheetFunction.Bitrshift(a, b)
ElseIf c = "NOT" Then
    Operate = 65535 - b
ElseIf c = "" Then
    Operate = b
End If
End Function

This function, basically put together all bitwise function needed (BITAND, BITOR, BITLSHIFT, and BITRSHIFT), with addition of BITNOT (there is no such Excel function) and direct signal. Therefore, I just need to put this formula in column H:

=Operate(IFERROR(INDEX($H$4:$H5,MATCH(B6,$F$4:$F$342,0)),B6),IFERROR(INDEX($H$4:$H5,MATCH(D6,$F$4:$F$342,0)),D6),C6)

The function IFERROR is needed because sometimes the variable (1 or 2) doesn't match with variable 3 and the formula returns an error value. The output is what I get in the last row.

Part 2

Description

Now, take the signal you got on wire a, override wire b to that signal, and reset the other wires (including wire a). What new signal is ultimately provided to wire a?

Solution

For the part 2, I put the value of  b from the part 1 in the place of 44430 and calculate a using the same formula as above.