F9 & REPT Function

[cimofj2]

I don't know if this is a problem or if I'm just being lazy, but I was exploring the use of the F9 Key.
I basically have 2 lists:
1) Each Alphabetic character-------: {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}
2) Repeat count for each character: {0,0,0,0,1,0,1,0,2,0,0,2,0,5,4,0,0,6,1,3,1,0,3,0,0,0} (Shown as a Named Range)
I tried using the following Formula:
=REPT({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},{TBLCurrentCounts})
I got the typical invalid formula error.
When I WAS able to get some kind of result, instead of accepting the formula, I got the following result:
{"","","","","E","","G","","II","","","LL","","NNNNN","OOOO","","","RRRRRR","S","TTT","U","","WWW","","",""} & the cell was just blank.
The question is, WHY this result? ? ?

 

[jasonb75]

Rept is only creating the array, you need to use =CONTACT(REPT(.....)) to display the whole string in one cell (that is assuming that you have a new enough version of excel).

With a function that can summarise the elements of the array (e.g CONCAT for text, SUM for numbers) only the first elemtent of the array will be shown in a single cell, in your case that is "" for zero repetitions of "A".

 

[cimofj2]

Yes, I've explored some other alternates & got similar results to yours and some other strange results.
I've played with other things like Ctrl + Shift + Enter, et. al.
Interesting stuff!
Going to revisit "Trick 1013" Video for more!

 

[jasonb75]

Ctrl Shift Enter makes no difference if the array is not nested in a suitable function.

If you enter your original formula into A1, then select A1:Z1, press f2, then Ctrl Shift Enter, your array will fill across the top row of the sheet.

If you replace the commas in the arrays with semicolons then you could do the same thing down the sheet in say A1:A26.

By mixing commas and semicolons it is possible to do the same with, A1:M2, with A to M in row 1 and N to Z in row 2. When you do this each row and column must have an equal number of elements. If, for example you attemt to drop your 26 element array into a 5 x 6 grid (30 cells) then it would result in errors.