Dick very recently put up a function that takes an Excel range and whips up some HTML to create a table. I loved that idea, and I asked if I could flesh it out. With Dick’s green light, I created a Sub() that captures each cell’s font family, font style, and font color, as well as the cell’s alignment and background color. It retains Dick’s option to use headers or not.

The sub spits the table to the clipboard. To get it there, in the VBE use Tools/References and check the Microsoft Forms 2.0 Object Library.

The table is a mixture of HTML and deprecated HTML (I’m not a purist.) I played with capturing the font size, but never liked how it came out so I commented it out. In creating HTML or CSS, many times you need to uses double-quotes (“) around the parameters. You can get by without it if the parameters are a single word, Arial for instance, but not for Times New Roman. Getting double-quotes in a text string requires you to escape them with another set of double quotes, creating double double-quotes (“”) and my eyes start to cross. I trick I use is to define a string*1 constant DQ equal to double double double-quotes (“”””). And then where I want quotes to appear in the HTML or CSS, I just concatenate in DQ. I used it throughout the Sub().

 
In the above replace the ampersand-amp-semicolon with an ampersand, the .LT. with < (35 times) and the .GT. with > (34 times.) The macro accurately reproduced a very ugly selected range as this double-ugly table:

That table uses every color in the default Excel palette, and the fonts use most of them. The fonts in Row 5, from left to right, are:

• Courier new
• Times New Roman
• Verdana
• Comic Sans MS
• Georgia
• Tahoma
• Trebuchet MS
• Arial Black
• Impact

There are assorted alignments and number formats sprinkled throughout. If the spreadsheet cell is empty, the macro puts a non-breaking space in the table as a placeholder. There is a considerable amount of bloat in the output, as it’s all done at the cell level. v3.0 will swap out the message-box interaction with a form that allows you to pick only what you are interested in. It’ll be out someday. Seeing how long it took MS to get Excel to edit at the character level, that’ll never be out.

Somethings I learned about WordPress: WordPress prefers the colors in #HEX format. When I used RBG, WordPress would wrap the RGB(r,g,b) in double quotes, and then not honor it! The fix was to use the HEX() function and a leading octothorpe. It turns out, WordPress wraps all the parameters, anyway. I don’t know how to capture the heading colors (I suspect it takes an API) so for now, whatever is the DDoE WordPress default (looks like a pale beige to me) is what you get here. (The sub’s code actually sets Windows Classic for the headers, but it gets overridden by CSS. Just as well–doesn’t really look like a window.) And I don’t think WordPress does subscript well when there’s no text to subordinate to. Cell C2 is specifed as superscript and E3 as subscript/strike-through. All I can say is that it’s clear on the spreadsheet.

…mrt:roll:

The engineer who attempted teaching me MatLab used a short script to demonstrate its matrix manipulation prowess. MatLab (shortened from Matrix Laboratory) is built from the ground up to do matrix mathematics. I wanted to translate his script to VBA, to see, 1. If it’s possible, and 2. How Excel compares. This is the original MatLab script:

 
Above, GT is “Greater Than”, LTE is “Less Than or Equals” and &amp is an ampersand. Here is my translation.

 
Again, GT is “Greater Than” and LTE is “Less Than or Equals”. Make the substitutions after you paste in the code to a new workbook. We have to do this or the WordPress parser treats them as HTML tags and doesn’t present the complete information, trying as it does to helpfully shorten everyone’s code.

Some observations: You don’t dimension variables in MatLab; and case matters, with x being a different variable from X. MatLab can plot at least 70,000 points, while Excel is limited to 32,000. The Excel limit is supposedly a series limit, but I could never plot two series totaling over 32K points. MatLab doesn’t need a spreadsheet to plot, so if there’s a way in Excel to plot values without going through a spreadsheet, would someone please so describe. I don’t think you can. As expected, the Sheet1 ranges are the same dimensions as the ZX and ZY arrays, which are at the series limits, and allows copying the arrays to the spreadsheet.

Matrix Multiplication in VBA requires dimensioning the VB arrays as if they were cell arrays so that the worksheet function MMULT() can be used. Z() is a variant re-dimed to the dimensions of the matrix multiplication, meeting the specifications MMULT() requires in a spreadsheet–two rows by one column. I can’t make the code run on a Mac, where we’re stuck at VBA5. The Mac won’t accept the assignment of the array.

And finally, while it’s less than half the points, it’s much less than half the time to run. It’s almost instantaneous on my machine. That may mean the MatLab script is not optimum. I’ll pass on along any observations MatLab veterans may make.

Give it a try. You’ll see why it was chosen as a demo. It’s worth the trouble. A picture is worth one kilo-word, and you’ll have it in a blink.

…mrt

OR: The problem with VLOOKUP. Wikipedia gives us a table of birthstones, and I think just because it’s Wikipedia, there’s a trailing space after every entry. Pasted into a spreadsheet, the table looks like this:

 
(Only there for completeness, the mystical stones have Tibetan origin, and the Ayurvedic stones have Indian sub-continent origin.) If you want to know the modern birthstone for April, you might use the VLOOKUP() function, probably as follows:

• =VLOOKUP(“April”,A1:E13,3,FALSE)

“April” for the month, A1:E13 as the array to search, 3 as the column from which to return, and FALSE because Months are not in an alphabetic sort and you want an exact match. If you do, you’ll get a #N/A error, indicating no match. That’s because of Wiki’s trailing space after April in A:A. Lets define a name: Birthstones =Sheet!$A$1:$E$13. Proper syntax could be:

• =VLOOKUP(“April “,A1:E13,3,FALSE)
• =VLOOKUP(“April “,Birthstones,3,FALSE)
• =VLOOKUP(“April”&”*”,A1:E13,3,FALSE)
• =VLOOKUP(“April”&”*”,Birthstones,3,FALSE)

All Return “Diamond “. Note the added wildcard asterisk in the last examples. You can use wildcards in FALSE VLOOKUPs, and you can do it front and back: =VLOOKUP(“*”&”April”&”*”,Birthstones,3,FALSE). But what if you want to know which month has the modern birthstone of Opal? You can’t use VLOOKUP() at all, because it only looks to the right, and the first column is the one searched. When I first encountered this limitation, my fix would be to add a column F:F to the right edge equal to A:A, and look right. It works, but there is a much better way. Lets define some more names:

• Month =Sheet1!$A$1:$A$13
• Traditional =Sheet1!$B$1:$B$13
• Modern =Sheet1!$C$1:$C$13
• Headings =Sheet1!$A$1:$E$1

The MATCH() function searches a 1*n or a n*1 array and returns the 1-based number where the match is found. It takes a third argument which gives the match type. When the match type argument is 0 (zero), MATCH() looks for exact matches, and can take wildcards, functioning akin to VLOOKUP() when set FALSE. Looking for Opal in the modern birthstones could be:

• =MATCH(“*”&”Opal”&”*”,C1:C13,0)
• =MATCH(“*”&”Opal”&”*”,Modern,0)

The INDEX() function specifies an array and specifies how far down to go within which column of the array, and doesn’t care left or right. Remembering that MATCH() returns a number, the syntax could be:

• =INDEX(A1:E13,11,1) Opal being the 11th entry in C1:C13, and we thus want the 11th item from Column 1 (A1:A13)
• =INDEX(A1:E13,MATCH(“*”&”Opal”&”*”,Modern,0),1)
• =INDEX(Birthstones,MATCH(“*”&”Opal”&”*”,Modern,0),1)

All done knowing the Month is in Column 1. But you don’t really need to know even that:

• =INDEX(Birthstones,MATCH(“*”&”Opal”&”*”,Modern,0),MATCH(“*”&Month&”*”,Headings,0))
• =INDEX(Month,MATCH(“*”&”Opal”&”*”,Modern,0)) with no need to specify column.

Will return “October ” no matter where the Month column is. INDEX() and MATCH() work so powerfully together that I rarely use VLOOKUP() at all. Left or Right doesn’t matter.

…mrt

In Part 1 we ended up with Column D, a sorted list. One criticism was the many times we were counting the numbers in a column. We should improve it and only count once. Via Insert/Name/Define define Count_BB to =COUNT(Sheet1!$B:$B), and then select Columns C:D, and “Replace All” COUNT(B:B) with Count_BB. Column D should look something like this:

 
In Cell E1 we simply move D1 over. In E2 we check to see if D2 is not equal to the cell above it, or in other words D2 starts a new run of names. If it is equal (FALSE condition) we put empty text. Otherwise, we put D2. Fill down from E2 as far down as columns B, C, and D are filled. Column E now looks like this. We have removed the duplicates.

 
In F1 we check to see if there is anything of length in E1. If there is, put the row number, otherwise put empty text. As above, define Count_FF as =COUNT(Sheet1!$F$F). After filling down, Column F looks like this:

 
In G1 we again test the row number, but this time it’s against the count of numbers in F:F. If the ROW() is less than or equal to Count_FF, put the numbers from F:F there is ROW() order. Fill down as before. Column G looks like this:

 
Last Step. In H1 we again compare the row number to the count of numbers in F:F. If ROW() is less than or equal to the Count_FF, then index E:E (could also be D:D) the number of rows shown in G:G. Fill down as before. Column H looks like this:

 
The list that started in A:A is now sorted, de-duplicated, and collapsed to unique values, ready in H:H for whatever you might need as you write your fantasy novel. All you need to remember is to fill B2:Hn down well beyond any possible extent of A:A. This can be extended to handle several field records by indexing the appropriate columns at the two appropriate points.

…mrt

You can download self_sorting_names.zip

I haven’t posted in a good while. I’ve done over half the Euler problems, but haven’t had the time to research the high-numbered ones considering I might even understand them. Now that I’ve retired from my second career, and my third is only part-time, maybe I’ll get back to it. Today is something perhaps more useful…to have a list of names, specifications, part numbers, etc and have them self-sort, de-duplicate, and collapse without ever having to go to the Sort Menu. Add a member to the bottom, and it flows through. Replace or paste over the data, and Excel turns the crank. Unique values appear, and no VBA is involved. This was a monthly chore in Job #2 for sometimes as many as 8000 email addresses.

Assume in Column A are 1000 (fantasy) names, in random order. These names were generated by using this website four times. Rice University and Dr. Chris Brown have provided name generation tools for many spoken languages. Your Column A might look something like this:

The formula in B1 tests if there in anything of length in A1. I’ve found that the LEN() function always returns a value that agrees with my ol’ Mark 1 Mod 0 eyeballs, and empty text doesn’t mislead me. If there is something of length in A1, then COUNTIF() everything in A:A that is less than A1. This uses Excel’s lexicographic algorithms, and that opinion may differ from what you want, but I’ve never had a problem with it. Add to that count using mixed references the COUNTIF() of everything between $A$1 and $A1 inclusive that equals $A1. When we fill down this will become COUNTIF($A$1:$A2,”=”&A2) in A2. Otherwise, if there is nothing of length, place empty text (“”-double double quotes). Fill down well below the extent of values in A:A.

Column B now looks something like this:

The formula in C1 tests if the row number is less than or equal to the count of the numbers in Column B. If it is, put the the smallest number there from Column B:B in ROW() order, otherwise put empty text. After filling down as far as in Column B, Column C is just 1 through the extent of your data in Column A. The use of SMALL() is what accommodates blank cells in A:A.

The formula in D1 tests the ROW() against the COUNT() again, and if ROW() is less than or equal to the COUNT(), specify zero as the third argument and find the exact MATCH() of the number in Column C:C within Column B:B, and then INDEX down Column A:A that far and return that result. Otherwise, put empty text. Fill down as far as in Columns B and C. After filling down, the list is sorted, and might look something like this:

Next post we’ll de-dupe the list and then collapse it to unique entries.

…mrt

Euler Problem 188 asks:

The hyperexponentiation or tetration of a number a by a positive integer b, denoted by a^^b or ^ba, is recursively defined by:

a^^1 = a,
a^^(k+1) = a(a^^k).

Thus we have e.g. 3^^2 = 3³ = 27, hence 3^^3 = 3²⁷ = 7625597484987 and 3^^4 is roughly 10^{3.6383346400240996*10^12}.

Find the last 8 digits of 1777^^1855.

Euler uses double up-arrows for hyperexponentiation. I substituted double carets as a “reasonable facsimile.” Tetration is covered by this Wikipedia article. A key point is to note that tetration is not associative, and we must evaluate the expression from right to left (top to bottom).

This is the recursive version:

This works fine, but it won’t handle 1777^^1885. Python has a Pow(b,e,m) function that returns base b raised to exponent e modulo m.

This is what we want to duplicate, particularly since returning the last 8 digits in to the same as modulo 10⁸. Here is the VBA translation of Pow(b,e,m):

I used decimal variants, so this will work for m-1 up to the square root of ~7.92e29, or about ~8.9e14. Big enough. BitShift in this case is integer division by 32. Here are those functions:

The usual angle brackets substitutions are above. Altogether then, this is the code for Problem 188:

Simple enough, but a lot of homework for this one. It put some more tools in the toolbox, and runs in less than 1/10 of a second.

..mrt

Summing the digits of a number is a chore I’ve been doing alot lately. Originally I’d parse the number out over the columns. And since SUM() ignores text, I’d turn the characters into digits by applying an arithmatic identity operation, like this:

• =- -MID($A10,COLUMN(),1)

That’s double minus signs before the MID() function. The reasons for picking that identity operation are here at XLDYNAMIC’s website, about half-way down.

And filling right. But if the numbers were of uneven length, filling down would throw a #VALUE! error for all but the longest number. Contrary to what the Help advises, I find that SUM() does not ignore error values. So I was ending up with this formula so I could fill down:

• =IF(ISERR(–MID($A10,COLUMN(),1)),0,–MID($A10,COLUMN(),1))

That’s double ugly, and a cell-eater to boot. I did a Google search and found Microsoft Knowledge Base article 214053 on this topic. Here’s what it says:

Formula 1: Sum the Digits of a Positive Number
To return the sum of the digits of a positive number contained in cell A10, follow these steps:

1. Start Excel 2000.
2. Type 123456 in cell A10.
3. Type the following formula in cell B10:
   =SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
4. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
5. The formula returns the value 21.

Ignoring Step 1, I looked at Step 4 and thought, from hanging around here, that we can do better. But to do better, let’s first look at the formula from the inside out. OFFSET() returns a reference one row less then A10 is long (more on OFFSET() later). ROW() then returns an array of row numbers starting from 1 (the row of A1–It’s the 1 that’s important, not the A) to the bottom of the offset. The array has as many elements as the length of the number in A10. MID() then creates an array of each digit as text. VALUE() turns the text into numbers, and then SUM(), array entered, sums the array of values.

While my formula was double-ugly, this one is just ugly. To impove it, from the outside in:

1. Replace SUM() with SUMPRODUCT(). The formula no longer has to be array-entered, and it works just as well.
2. Replace VALUE() with the double minus
3. Instead of using LEN(A10)-1 as a row offset, use LEN(A10) as a height parameter.
4. Make the reference to A1 absolute with respect to row, allowing fill-down.

The new formula is:

• =SUMPRODUCT(- -MID(A10,ROW(OFFSET(A$1,,,LEN(A10))),1))

Much prettier, and even not counting curly-braces, two characters shorter. The Knowledge Base goes on to give this as the formula for summing the digits of a negative number:

• =SUM(VALUE(MID(A11,ROW(A2:OFFSET(A2,LEN(A11)-2,0)),1))) also array-entered.

This is the better version, simply entered:

• =SUMPRODUCT(–MID(A11,ROW(OFFSET(A$2,,,LEN(A11)-1)),1))

You have to start the array at 2 (via A$2) to skip the negative sign, and then also shorten the length by one for the same reason. This one is the same length as Microsoft’s. If you want one formula for all numbers, this one has no counterpart in the Knowledge base:

• =SUMPRODUCT(- -MID(ABS(A11),ROW(OFFSET($A$1,,,LEN(ABS(A11)))),1))

It uses the absolute value ABS() function for the obvious reason. It only works for true numbers. It will not handle long text strings as numbers, such as you may have for credit cards or international phone numbers. For those, either use the earlier one, or use SUBSTITUTE(A11,”-“,””) in place of ABS(A11). Now we’re the ones getting getting ugly.

…mrt

Euler Problem 203 asks:

The binomial coefficients ⁿC_k can be arranged in triangular form, Pascal’s triangle, like this:

Visual Basic

1 1 1 1 2 1 1 3 3 1 1 4 6 4 1 1 5 10 10 5 1 1 6 15 20 15 6 1 1 7 21 35 35 21 7 1 .........

1 2 3 4 5 6 7 8 9

1                      1     1                   1     2     1                1     3     3     1             1     4     6     4     1          1     5     10    10    5     1       1     6     15    20    15    6     1    1     7     21    35    35    21    7     1 .........

It can be seen that the first eight rows of Pascal’s triangle contain twelve distinct numbers: 1, 2, 3, 4, 5, 6, 7, 10, 15, 20, 21 and 35.

A positive integer n is called squarefree if no square of a prime divides n. Of the twelve distinct numbers in the first eight rows of Pascal’s triangle, all except 4 and 20 are squarefree. The sum of the distinct squarefree numbers in the first eight rows is 105.

Find the sum of the distinct squarefree numbers in the first 51 rows of Pascal’s triangle.

Each number inside Pascal’s Triangle is the sum of the two numbers above it. Pascal’s Triangle, traditionally zero-based, can be portrayed in an array, like this:

In this form, each number below row(0) and right of column(0) is the sum of the number diagonally left and above with the number directly above. There is another way to determine each number, which is what Euler’s ⁿC_k notification signifies. Where n is the row, and k is the column, each number is n!/((n-k)!*k!). In the problem statement, the largest n is 50. Therefore the largest prime-squared we have to deal with is 49, and the largest prime is 7. We have no need to investigate higher.

Once we build the 0×50, 0×50 Pascal array, a collection is the perfect tool to collect(duh!) unique entries because you can’t have duplicates; and because of symmetry, we only need look in the left half of the array. Finally, loop through the distinct numbers checking the remainders from the divisions by 2², 3², 5², and 7². If all of them are non-zero, the distinct number is square-free, and add it to the running total to form the answer. Equivalently, as implemented, if any of them are zero, then square-free is false, and don’t add the distinct number.

This is the code that does this. It runs in about 2/100^ths of a second, and builds the “triangle” by addition rather than factorization.

It’s probably overkill to use a sieve for only 4 prime numbers, but it’s a tool the tool box now. I wanted to use “distinctnum(n) mod p*p” but that throws errors for large values of distinctnum(n), so I went with definition of mod instead.

…mrt