Reply To: Challenge 10B
@BobD
This was my approach too, as the advantage BobD mentions is really useful.
For those interested, I used SUMPRODUCT to find the Row and Column of each of the two cards:
-The cells to the side of the table, that is 0-9 running down the rows = row range.
-The cells to the above the table, that is 0-9 running across the top = column range.
-The cells covering all the cards, AC through to KS = card range
-Arrange the cards in groups of 4 running down a spreadsheet e.g.
7CXS
3H6S
7CKS etc
You can then extract “7C” with Left(card pair,2) and CX with Right(card pair,2). I have type 7C below for ease.
– The row is given by SUMPRODUCT((“7C”=card range)*(row range))
– The column is given by SUMPRODUCT((“7C”=card range)*(column range))
Using BobD’s table: Card 7C is row 1 and column 0, and Card XS is row 9 and column 6.
You can then use INDEX to extract the decrypted pair of letters ‘RE’
– the R is INDEX(card range, 9, 0). This is the row of XS and the column of 7C.
– and E is INDEX(card range, 1, 6). This is the row of 7C and the column of XS.
You can then just copy these INDEX formula down all the card pairs. Finally you can join the decrypted text together to extract it as a text block to put in to words and sentences.
Note: Just be careful to make sure the references to the ‘row range’, ‘column range’ and ‘card range’ are ‘fixed’ correctly so that they always reference the card table.