Find out the number of unique products in the list (First formula Explained)

For those who are wondering what all those excel formula meant, here is the explanation:
Before I start explaining the complex format, here is how sumproduct was meant to do

Assuming there are 2 sets of numbers as follows:

    A     B
1   6   10
2   3   5

When you entered the Excel Function =SUMPRODUCT(A1:A2,B1:B2) into one of the cells, say D1, you get a result of 75. What the excel function does is that it will multiply A1 with B1 (6 x 10) and A2 with B2 (3 x 5). After which, it will add up the 2 results and give you 75 (60 + 15).

    A
1. Excel
2. VBA
3. Internet Marketing
4. What is a Blog
5. What is a Blog
6. Internet Marketing
7. What is a Blog
8. VBA

In our unique list, the formula initially started as simple as this =sumproduct(1/countif(A1:A8,A1:A8)).
What the countif [countif(A1:A8,A1:A8)]does is actually a shortcut way of populating the formula [=countif(A1:A8,A1)] in B1 and copy the formula all the way down to B8. The results would have been: 1 count of the word Excel (which appears in A1) is found in the list, 2 counts of VBA is found in the list, etc. As the function is inversed, the result turns out to be like this:

   A              B

1. Excel           1/1

2. VBA           1/2

3. Internet Marketing           1/2

4. What is a Blog           1/3 (1/3 because there is 3 counts of the phrase "What is a blog")

5. What is a Blog           1/3

6. Internet Marketing           1/2

7. What is a Blog        1/3

8. VBA           1/2

As a result, the number of unique records are returned,

Excel = 1/1 = 1
VBA = 1/2 + 1/2 = 1
Internet marketing = 1/2 + 1/2 = 1
What is a Blog = 1/3 + 1/3 + 1/3 =1

The total is 4 unique records.

This works perfect provided that the list do not contains blank records. If not, the result 1 divided by 0 would have returned a #div/0! error To circumvent that, the following are added. 1 is changed to [A1:A8<>""] to tell the formula to ignore blank records.

Likewise, the following [(A1:A100="")] is added to make sure that denominator will return a value greater than 0. In this case, when the list contains more than a blank record, the denominator is more than 0 because of this segment of the formula + (A1:A100=""). If the list do not contain any 0, it will still return a number based on the formula COUNTIF(A1:A100,A1:A100). Either way, the denominator will return a value of more than 0.

Find out the number of unique products in the list

Assuming you have a list of product names in a column, say one hundred rows. You want to find out how many unique items are there in the list. To do that you can use the following formula:

=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

This is the simplest formula to use. Another you could use is as follows:

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

The second formula is longer but will return the results faster. According to one web site, it is 4-5 times faster. If this is a simple worksheet, you may not feel the difference. However, if yoiu have a complicated worksheet with multiple formulas, the second one may prove to be a time saver if it really calculates 4-5 times faster.

Converting a number with decimals to fraction

I was have joined this yahoo group which talks about Excel topic. Somebody asked if he is able to convert a number with decimals to a fraction. As I explore the format cell, I noted that there is this category that could convert the number to fraction. Not only that, it also gave options on whether to convert the number to fraction based on on 1 to 3 decimal places. There are also other options. See this diagram for more details.

Hiding rows of expenses with zero value

My client has a list of monthly expenses that needs to be presented. To shorten the list for the presentation, those rows of expense items which have zero value need to be hidden. Imagine if the list is 100 rows and out of which, 20 rows do not have any value and are scattered among the 100 rows of expenses. What my client did was to go thru the expenses row by row and hide the expenses with $0 value one by one. She has to repeat the action 20 times if there are 20 rows of expenses with zero value. After I showed her find all, she just need to do it once. That is a saving of 95% of her time. Here's how if you have not figured out from previous post.

Highlight the column with the expense values. Activate the find function from the menu by selecting edit, find. Type in the number zero and click on the option and check the option box (Match Entire Cell Content) and click find all. Highlight the results and then close the find dialog box. Go to the menu and select format, rows, hide. And all the expenses with zero value are hidden.

Hiding column between merge cell

I was in my client's office the other day and she told me that she cannot hide the column when there is a merge cell in one of the rows. For example, there is a merge cell D1:F1. She wants to hide the column E. What she did was to click on the column header which highlight the entire column. As D1:F1 is a merged cell, the entire merged cell is highlighted. When she clicks on the right mouse button and chooses hide, a error message pops up. The error is due to the merge cell. As such, she has to resort to unmerge the cells D1:F1, hide the column E and remerge the 3 cells.

There is an easier way and here it is.

Select any cell in column E other than that in row 1 which contains the merge cells. For example, E2. Then go to the menu bar and select format, column, hide. The column E is hidden. Don't need to unmerge the cell at all.