UNIQUE Random Strings of Letters

This is about creating strings of up to 26 letters with no repeats of any letter, using an array formula. Here's the setup:

How to count any characters and digits in a text string using ARRAY formula

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:

Summing up a variable number of the Largest or Smallest values in a range

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.

SUMMING UP selectively based on references - using ARRAY FORMULAS

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:

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

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.

In Reverse: Going Backwards with ARRAY formulas

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.

 

How to Check if the Contents of Two Ranges/Worksheets is Different

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.

To COUNT any Characters or Strings in any Range of Cells

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:

Complex Array formula

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:

• create quickly sample datasets
• count some values contained in a range of cells
• sum numbers that meet certain conditions, such as the lowest values in a range
  
• sum every Nth value in a range of cells
• find specific values in a cell or a range of cells
• etc.   
  

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):

Reference the last cell in a column

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.

 

Generate unique random alphanumeric characters and strings

If you play lottery games of lotto type or want to get a list of some unique random numbers, letters or expressions, Excel is very helpful in generating them. There are many ways to accomplish that. Here are just couple of them.

To create a list of unique random numbers using an array formula

First, format a sellected cell, e.g. A1, as Text. Enter a range of numbers from which to draw, in this format: 1-50 . Next, enter in cell A2 the following array formula:

=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).

To create a list of unique random numbers using UDF (user defined function)

• Press ALT+F11 on your keyboard to open up the VB Editor.
• If no Module is available within your Project, then right-click within the VBA Project Explorer and select Insert, then choose Module from the menu.
• Enter the following code in the module:

Function GetUnique(Lo As Integer, Hi As Integer, Nr As Integer) As String
'Generates x unique random numbers between any 2 numbers you specify
'e.g. =GetUnique(1,50,8) produces 8 unique random numbers between 1 and 50
    Dim iArr As Variant
    Dim i As Integer
    Dim r As Integer
    Dim temp As Integer
    Application.Volatile
    ReDim iArr(Lo To Hi)
    For i = Lo To Hi
        iArr(i) = i
    Next i
    For i = Hi To Lo + 1 Step -1
        r = Int(Rnd() * (i - Lo + 1)) + Lo
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    For i = Lo To Lo + Nr - 1
        If i = Lo Then GetUnique = GetUnique & "" & iArr(i)
        If i > 1 Then GetUnique = GetUnique & " * " & iArr(i)
    Next i
    GetUnique = Trim(GetUnique)
End Function

• Use the function in any selected cell by entering it in the format shown above on the comment line.

To create a random letter of alphabet (eithe Upper- or Lowercase)

Use this formula:  

=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))) 

It generates a random letter from a to z and A to Z characters.

To create a string of random letters,

like in this example:

 

 

 

 

you need to use "&" character to add the letters together:

=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.