excel arrays are with me in exceltoxl (part 3 of 4)

« Excel Arrays are like Ladies Costumes ;) Array_Week (4 of 4)

Excel Arrays are like Ladies Costumes ;) Array_Week (2 of 4) »

Oct 25

Excel Arrays are like Ladies Costumes ;) Array_Week (3 of 4)

Hi Folks,

We all know there is a feature in Excel called arrays. This is an interesting topic to learn and very challenging either because its an amazing and powerful concept of Excel features. Arrays are like ladies costumes, because it is difficult to understand or to explain to some one about the concept of arrays, similarly the costumes available in the market for ladies. Simple example here is we go for shopping with our girl friend(s) (rarely with wife, right? ;) ) and we really can't expect the budget they spend and the new company brands they choose every time we go. In addition, even companies are also introducing many products to make our ladies look like Angels, right?. In one sentence, Excel Functions create magic with Array features similarly, the ladies create magic with costumes. I agree, do you???

However, let us try to understand the functionality of arrays the way we understand our ladies. ;) Now, let us see few topics about Excel Arrays with repect to Excel Functions.

...

FIND DUPLICATES USING ARRAYS:

In a range of cells to find whether the list has duplicate values or no just follow these steps:

Firstly, use COUNTIF function to find if there is any duplicates

Secondly, use MAX function to find the maximum duplicates in the list

Thirdly, use IF function to replace the maximum number with either "Unique List" or "Not Unique List".

Now, we will see the complete formula here:

=IF(MAX(COUNTIF($A$3:$A$13,A3:A13))>1,"Not Unique List","Unique List")

Once the formula is entered before pushing ENTER as mentioned earlier push CTRL+ SHIFT + ENTER to apply arrays feature.

HOW FORMULA WORKS:

COUNTIF: countif will count the number of times the value is repeated.

MAX: max function will display what is the maximum duplicate number in a range

IF: if function change the value of the range to either "Unique List" or "Not Unique List"

Here, Max function is required because if we use IF function directly then the range will show Unique List and Not Unique List depends on the non-duplicates and duplicates right after the cell.

The example list shows as below:

Duplicate Formula:

ARRAY TIPS:

To select the array range, use CTRL + / (back slash)

To remove the array feature, use F2 to edit the array cell and press CTRL + ENTER

The below table shows how array formulas works with its components or arguments expands.

Points to be Noted:

If an array formula produces an array smaller than the selected array range, then Excel expands the resulting array to fill the range.
If Excel expands an array to fill a range larger than the array formula, #N/A error values appear in cells for which no valid expandable value is available.
If an array formula produces an array larger than the selected array range, the excess values do not appear on the worksheet.

For example, the formula ={ 1,2,3}*{4,5,6} produces the l-by-3 array {4,10,18}.

If you enter this formula into a 2-by-3 array range, Excel expands the result to {4,10,18;4,10,18}.
If you enter the same formula into a l-by-4 array range, Excel expands the result to {4,10,18,#N/A}.
If you enter the same formula into a l-by-2 array range, the result is {4,10}.

DID YOU WORK ON ARRAYS?

Do you work on array formulas in your work. Did you find something challenging while working on arrays. Then, please use this space to share your ideas, feelings, comments, achievements and what not any experiences about this topic in the comment section below.

If you like this post then please do share this post with your friends, relatives, colleagues and of-course with co-hearts to enhance their skills with your help.

If you want to support me for maintaining this blog and to receive latest updates from exceltoxl.com then, please do REGISTER HERE for free. Login details are kept confidential from the public.

SOME IMPORTANT LINKS:

Count Colored Cells using simple vba

Count Worksheets in a Workbook

Formulas Cheat Sheet (Buper Bonanza)

OFFSET function tricks are revealed

Cooked Dynamic Charts (3 flavors)

Thank You,

You must be logged in to see the comments. Log in now!

If you have no account yet, you can register now! (It only takes a few seconds)