This is about creating strings of up to 26 letters with no repeats of any letter, using an array formula. Here's the setup:
This is about creating strings of up to 26 letters with no repeats of any letter, using an array formula. Here's the setup:
Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.
Here is an example of a string located in cell A2:
Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.
I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here:
Here is a range of numbers provided as an example. It could be any unsorted range/list of numbers you're working on:
Your data set is large and for some specific analytical reason you'd like to track the sum of several largest and smallest numbers in your set. The ARRAY formulas come to help and are very efficient in such cases.
Some business sells some products, e.g. vegetables, to various countries/ merchants. Records of sales are kept in Excel table, like in this simple example:
Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:
Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.
Do you need to type or use expressions or numbers in reverse?
Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:
=SUM(VALUE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*10^(ROW(INDIRECT("1:"&LEN(B2)))-1))
However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.
So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:
The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.
You are not sure if some ranges of data in your worksheet, or in two different worksheets, contain the same data. How to figure it out? And if they are different, is the difference significant?
Use ARRAY formula to check it out. You can count the differing cells using e.g. this kind of array formula in Sheet1 to compare with cell contents in Sheet2:
=SUM(IF(B2:G100=Sheet2!B2:G100,0,1))
Remember that to enter it you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets.
Also, make sure that the size of compared ranges is the same. Location, however, can be different, like e.g. A2:F20 in one worksheet and C5:H23 in another.
Use an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.
Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:
=SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))
and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1:
An array is a row or column of values, or a combination of them. Array formulas can be very useful in many applications where normal Excel formulas don't work. An array formula is a formula that can perform multiple calculations on one or more items in an array.
Array formulas can return either multiple results, or a single result. We can use array formulas to perform complex tasks, such as:
The following example shows you how the single-cell array formula can find the last filled cell in a given row and show its value. Here is the formula (all entered in a single cell):
If you need to reference the last cell value in a given column, use the following array formula:
=INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))
It returns the value held in the last filled cell in column G.
If you need to find out also what the row number of the cell keeping the value is, then this array formula can be used:
=CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>""))))
You may want to get both the value and location / row number of the last cell. In such a case you can combine the above formulas into one, like in this example:
=INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&" ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>""))))
Sometimes the formula can return an error value. It may happen, if any of the cells in the column displays an error. To make you aware of such an error, you can include error checking part, e.g.:
=IFERROR(INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&" ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))),"Error in the column!")
Replace G in the formulas with your actual column of interest, as needed, and keep in mind that these are all ARRAY formulas, so you need to hold simultaneously CTRL+SHIFT keys and press the ENTER key to create them.
=SMALL(IF(COUNTIF(C$1:C1,ROW($1:$50))<>1,ROW($1:$50)),1+INT(RAND()*(RIGHT(C$1,LEN(C$1)-FIND("-",C$1))-LEFT(C$1,FIND("-",C$1)-1)+1-ROW()+ROW(C$2))))
To enter it correctly, hold CTRL+SHIFT and press ENTER key on your keyboard. If you need to generate random unique numbers from a different range, you have to replace the numbers (1 and 50) in both A1 and A2 cells.
Now you need to copy cell A2 and paste it to as many rows as many random numbers you are expecting to obtain (up to 50 in the example).
It generates a random letter from a to z and A to Z characters.
like in this example:
=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))
This specific formula generates a string of three random letters. If you drag the cell fill handle over to some range you want to cover with this formula, you will get the range filled with random 3-letter strings. The formula can be quite easily modified to get unique random strings including any and many (more than three) alphanumeric characters.