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.

Quick Calculation of highlighted range

Enter the following values into a new worksheet

A B

01 20 10
02 5 15

The first row is the column label and the first column shown is the row label. Highlight Cell A1:B2. Have you ever notice that the total sum of 50 is shown near to the botton right hand corner of the spreadsheet. You don't need to enter a sum formula to find the total.

And if you have known that, then do you know that you could let the results shows the average of the 4 numbers, i.e. 12.5. How do I do that? Point your mouse at the status bar. (If you don't see the status bar, you could go to menu, view and activate the status bar option). Click on the right mouse button and you could see the list which allows you to select whether you want to see the sum (as shown above), average, etc. Select the one that you uses most often and start calculating how much time you have saved with this new discovery.

Removing Hyperlinks (All at one go)

If you want to remove a hyperlink from one of the cells, what you need to do is to point at the cell using the mouse and click the right mouse button. In the floating menu, select "remove hyperlink". Done.

The option above will not work for multiple hyperlinks. If you have ten links, you have to do the action ten times.

Now, here is the solution to solve the above multiple links problem.

1. Enter the number 1 in a blank cell
2. Copy the number 1.
3. Highlight the cells that contain the hyperlinks.
4. From the menu, select edit, pastespecial.
5. In the Paste Special Dialog box, select the mulitply radio button. Click OK.
6. All the hyperlinks are removed. Delete the cell with the number 1 created in step 1.

Group columns for instant hiding and unhiding

There are times when you need to hide a group of columns. At other times, these columns needs to be unhidden and another set of columns needs to be hidden. If you are in such a situation, this is something you should look at.

 

Assuming below is the layout for a worksheet.

 

                A        B        C        D        E        F        G        H

1

2

3

4

5

 

To hide column A, E and F at one go, this is what you should do.

 

1. Go to Cell A1. From the menu bar, select data, group and outline, group. In the popup box named group, select columns.

2. Highlight the cells E1 and F1. From the menu bar, select data, group and outline, group. In the popup box named group, select columns.

3. You will notice that there are 2 buttons called 1 and 2 on the top left hand corner above the column labels. See diagram.

When you click on 1, the column A, E and F will be hidden. When you click on 2, the columns will appear again. You can try grouping more columns and see how the presentation changes.

 

Multi-Dimensional Sum

Assuming you have a set of data as shown:

A B C
01 ABC XYZ 10
02 ABC XYZ 20
03 DEF XYZ 30
04 PQR ABC 40
05 UVW XYZ 50

You want to add up those numbers that satisfy the conditions Column A contains "ABC" and Column B contains "XYZ". So what are some of the solutions?

Solution 1
1. Enter a subtotal function in cell C7. The formula should be "=subtotal(9,C1:C5)". The cell C7 is chosen instead of cell C6 so that when you do the autofilter later on, it will not be included in the autofilter.
2. Do a autofilter and set the criteria for column A as "ABC" and the criteria for column B as "XYZ".
3 Cell C7 should return the result 30 (10+20)

Solution 2
Enter in cell C6 (or anywhere you prefer) the formula "=sum(if(A1:A5="ABC", if(B1:B5="XYZ",C1:C5)))". Instead of the normal enter key, you need to use shift + ctrl + enter. This is because it is an array formula. And you should get the same results as solution 1 (30).

Comparing two list with similar list of items

Now that you have learnt the required functions for comparing the two lists, we can now show you how to do the comparison.

 

Assuming you have 2 lists as shown, each residing in one worksheet.

 

List 1

Excel	good
Excel VBA	good
SynergyWorks	good
Internet Marketing	good

 

List 2

Excel VBA	good
Synergyworks	good
Adsense	good
Adwords	good

   

Both lists have items of their own (Excel, Internet Marketing in list 1 and Adsense, Adwords in list 2) and items that are available to both of them (Excel VBA). You need to compare the 2 lists and find out which item is present in either list or both.

 

For this to happen, first combine the 2 lists and put them into fresh new worksheet as shown.

 

Excel

Excel VBA

SynergyWorks

Internet Marketing

Excel VBA

SynergyWorks

Adsense

Adwords

 

Do a sort so that similar items are listed together. To remove the duplicate items, you must use the If function in the next column. The following table starts from A2 and A1 is a blank cell. Enter the formula "=IF(A2=A1,"Duplicate","")" as show in the digram below. Copy the formula down the list.

 

Adsense	=IF(A2=A1,"Duplicate","")
Adwords
Excel
Excel VBA
Excel VBA	Duplicate
Internet Marketing
SynergyWorks
SynergyWorks	Duplicate

 

The formula will flag out the duplicates for Excel VBA and SynergyWorks. Do a autofilter using the criteria "blanks" and the duplicates are sieved out.

 

Highlight the entire list and press Function key F5, go to special cells and select the option visible cells only and click "OK".

 

Click on the copy icon or go to the menu bar and click on edit, copy.

 

Paste the results in a brand new worksheet. You have a list of unique items.

 

Use vlookup and lookup the value in the repective cells as shown. replace the word "list1" and "list2" with the relevant ranges. "=VLOOKUP(lookupcell,list1,2,FALSE)". Assuming that adsense is in cell A2, then lookup cell is A2. 

 

	List 1	List 2
Adsense	#N/A	good
Adwords	#N/A	good
Excel	good	#N/A
Excel VBA	good	good
Internet Marketing	good	#N/A
SynergyWorks	good	good

 

To beautify the results, you could extend the formula using if function ---> "=IF(ISNA(VLOOKUP(B2,Sheet1!$B$2:$C$5,2,FALSE))=TRUE,"",1)" It means that if the result is "#NA", show a blank cell "", else show the number 1. ANd you have got a table which show which word/phrase exist in which table as show below.

 

	List 1	List 2
Adsense		1
Adwords		1
Excel	1
Excel VBA	1	1
Internet Marketing	1
SynergyWorks	1	1

 

That's the end of this topic. Hope you enjoy this series.

 

 

Comparison of 2 lists (Preparation 3)

Vlookup is the 3rd function that you need to learn. Vlookup stands for vertical lookup. What it does is that it will look up a set of values and then return the corresponding value in another column.

Header 1	Header 2
Internet marketing
Synergyworks	Good
ExcelWorks	Good
MS Words
Blogging	Good
Testing

Assuming the two list starts from Cell A1 (showing the word "Internet marketing"). The list is ranged between A1 to B5. Therefore, you could lookup the value say "ExcelWorks" and have the word "Good" returned as the results. The syntax is =Vlookup("ExcelWorks",A1:B5,2,false). What the formula meant is that look up the value/word "ExcelWorks" in the range "A1:B5",return the value in column 2 and if there is no value found, do not return the closest results. In this case the word "Good" will be returned. If you search the word Excelworking, "#N/A" which is an error value is returned.

Comparison of 2 lists (Preparation 2)

The goto function or F5 function key is known to most users to move from one cell to another by typing in the cell address. For those who have explored further, they would have found that the F5 function can do a lot of wonders and save them significant time in data management or reorganising the data they have.

This is done thru the "special" button found at the bottom left corner of the goto dialog box. Assuming that you have a list as shown below:

Header 1	Header 2
Internet marketing
Synergyworks	Good
ExcelWorks	Good
MS Words
Blogging	Good
Testing

Using the auto filter, filter those items that have the word "Good" in Header 2.

Side track to auto filter function
For those who don't know how, goto within the list say select the cell containing the word "Header 1". Then goto to Data, Filter and select AutoFilter. Two buttons apeears at the right side of both cells containing "Header 1" and "Header 2". Click on the button on the right of the cell containing the word "Header 2". Select the word "Good" from the list. (See diagram) The list is filtered showing those with the word "Good" under "Header 2".

Instead of using the normal means of highlighting the selected range and click on copy (which is what most basic users will do), let's press the F5 function key and then click on the Special button. Select the option "Visible Cells only" (see diagram). Click "OK". Only the visible cells are selected for now. So what can you do after that? You can do wonders with this. One of them is to color the selected cells while leaving the rest as they are. You can try it out. For the purpose of the comparing 2 lists later, we will copy the cells and paste it to another location.

That's all for today. See you tomorrow.

Comparison of 2 lists (Preparation 1)

Before we can compare 2 lists of items, we need to learn some new functions. One of them is the if Function. The syntax for the if function is "=if(define the condition, return a result if the condition is true, return another result if the condition is false).

For example if you have a list of 2 words,

(Cell A1) Excel
(Cell A2) SynergyWorks

In cell B1, enter the formula =if(A1="Excel", "It is true that the cell contains the word Excel", "This cell does not contain the word Excel").

In this case, B1 will return the true statement and B2 will return the false statement.

That's all for now. We will cover another function tomorrow.

How to put labels into the cells without forgoing the formulae

Assuming you have a list of values as shown:

Value 1	Value 2	Total
34	(3)	31
23	66	89

The first set is actually calculation for hrs and the second is for metres. You wish to make them more meaningful by putting the discription "hr" to the first set of values and "m" to the second. For most users, they would have make the calculation first, convert them to values and then input the metrics into these values. By doing so, the formulae are wiped out.

If you do not wish to eliminate the formulae, here is the solution for you:
1. Set up the table as shown.
2. Add up the numbers or input whatever formula you have for the total column.
3. Highlight the first set of numbers, 34, (3) and 31.
4. Go to format, cells.
5. Select the numeric form you wish to present the data, e.g. if you select the number category, choose 0 decimal places, use thousand separators, click on brackets and red color for negative values. Click here for diagram.
6. Now select the category "Custom"
7. You should see the format "#,##0_);[Red](#,##0)" in the type box.
8. Add in the word hr after the zero and after the bracket for negative values as shown #,##0 "hr"_);[Red](#,##0) "hr". If you are conversant in the numeric format, you can skip step 5.

9. Click ok and you should see the numbers presented as below. The formulae are retained while the presentation has changed.

Value 1	Value 2	Total
34 hr	(3) hr	31 hr
23	66	89

10. Change the second set of numbers to include "m" after the numbers by repeating step 3 to 9.

The power of Find All uncovered revisited

The last time, we share about using FindAll to find cells with a particular word or phrase and replace those cells completely with a brand new word or phrase using Ctrl + Enter. This method can be extended to replace a series of cells. This is what I have encountered and solved. Use this file for your practise.

1. First highlight and copy the series, e.g. in diagram 1, highlight the range "B6:F6", and click copy.
2. Highlight the the entire column B and activate the find function.
3. Type in the text to find, in this case, "Company"
4. Click on the FindAll button.
5. Highlight all the results. Please see diagram.
6. Close the find and replace dialog box.
7. Like the last time, use Ctrl + Enter.
8. The contents in all the rows (row 13, 20 and 27) are cleared off except the word "Company" which is found in column B.

How to hide Sheet2 without being discovered

1. First Go to Tools, Macro, Visual Basic Editor as shown in diagram 1.

2. In the Visual Basic Editor, click on Project Explorer and Properties window if you do not have the layout shown in diagram 2.

3. In the project explorer, expand VBA Project (Book 1) by clicking on the "+" sign on the left of the label. Click on the "+" on the left of the label Microsoft Excel Object.

4. Select Sheet2 ( See diagram 3). When you have done that, you should see some details as shown in diagram 3.

5. Click on the last property called visible and select the option 2-clVeryHidden. (see diagram 4)

6. Close the visual basic editor.

7. Sheet2 has disappeared. Try to check to see if it is available for unhide in the normal Format, Sheet, unhide.

 

Hope you are amazed by this revelation.

 

 

 

 

 

What experts don't tell you about hidden worksheets (revealed)

Did you managed to find the content hidden in Sheet2?

If you have not, here's how. Enter the formula "=Sheet2!A1" in Sheet1. Copy and paste the formula in the range "A1:M20". You should be able to see text "BINGO. YOU HAVE MANAGED TO FIND THIS WORKSHEET." in Cell C5.

To make it nicer, you can go to Tool, Options and click on the view tab. Click off the check box on zero value. (See diagram). The zeros all disppear showing the text on Sheet1 clearly.

So how did I make sheet2 disappear such that it will not even show up using the Format, Sheet, unhide approach? Find out tomorrow.

What experts don't tell you about hidden worksheets

Download this excel file and find the content stored in the hidden sheet2. Come back tomorrow if you don't know how to do it.

Setting Date Format

The most common date format used by many are dd-mmm-yy or dd/mm/yy or mm/dd/yy. What has been neglected or remain unknown to most users is that Excel can return the day (e.g. Mon) of the date. When you entered "ddd" in custom format for a particular cell that contains a date value, the day is returned (see diagram). If you entered "dddd", the full string (Monday) is returned. If you entered mmm, the 3 characters month name is return. By entering 4 "m", the full month name (e.g. January) is returned.

Using a list of recipients in Excel to send email

Imagine you are given a list of email addresses in an Excel worksheet (sample as shown).

abc@baxter.com

def@nea.gov.sg

ghi@hotmail.com

jkl@yahoo.com

mno@hotmail.com

pqr@yahoo.com

stu@gmail.com

You are told to send email to all the names in the list. Are you going to send the email one by one? If you are, then there is a better solution for you. You can create a email list thru the use of Excel Function called concatenate or simply use the symbol "&". Here is how:

1. Enter the formula to reference the first email address. Asuming that the first email address is in cell A2, then enter the formula "=A2" in cell B2.
2. In cell B3, enter the formula "=B2&";"&A3". The "&" in the formula is to join the content in cell B2 together with Cell A3 and at the same time add the symbol ";" between them. (see diagram)
3. Copy the formula down to the last row.
4. Copy and paste the content in the last row as values.
5. Copy the list of email addresses and paste it in the To, CC or BCC field in your email message.
6. You are ready to send the info to all those in the list.

The power of Find All uncovered (Part II)

If you are amazed by what I have revealed yesterday, read on. Here is another application for the Find All function.

Let's use the same list given yesterday. Instead of deleting the row, I will show you how to replace those cells with the statement "I want to replace this cell with this statement" in less than 10 seconds.

For those who did not have the list, it is given below. Please put them into a new worksheet.

internet marketing software
microsoft internet marketing
internet marketing filter
internet marketing program
free internet marketing
free internet marketing software


1. Highlight the list in the Excel Worksheet.
2. Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3. Type in the word "software"
4. Click on the "Find ALL" button
5. Select the list by
a) Go to the first item in the list.
b) Hold on the shift key and click on the last item in the list.
c) You have highlighted the entire list (See diagram)
6. Close the find and replace dialog box.
From here, type the statement "I want to replace this cell with this statement" into the Formula Bar. For those who do not know where is the formula bar, it is located just below the icons and to the right of the symbol fx.

The power of Find All uncovered

In the find and replace box, there is a button that is seldom used by most users. That is the Find All function. It is a very powerful function if you know how to make use of it. And here is one of its uses:

Assuming that you have a list of words as shown below:

internet marketing software
microsoft interne marketing
interet marketing filter
internt marketing program
free intrnet marketing
free interet marketing software


You wanted to delete the rows if the word "software" is found within the phrases. One of the simplest and tedious way is to go thru the list one by one and when the word is found, delete the row.
Here is a faster way.
1. Highlight the list in the Excel Worksheet.
2. Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3. Type in the word "software"
4. Click on the "Find ALL" button
5. Select the list by
a) Go to the first item in the list.
b) Hold on the shift key and click on the last item in the list.
c) You have highlighted the entire list (See diagram)
6. Close the find and replace dialog box.
7. Delete the entire rows using "Ctrl -" (shortcut key)
8. Now you have a list without the word "software"

Maximising the use of Find and Replace

The find and replace feature is used to replace word(s) or phrase(s). And most people only know how to use this feature to replace full word(s) or phrase(s). It may not occur to them that the usability of this feature could be enhanced by understanding a little bit more about the words or phrases or abbreviations you want to replace. Let me share with you what I mean.

Assuming if you have a long list of company names (a short list of 4 companies is shown below for illustration purpose).

ABC PL Co
EFR Placement Co
THG Gold-Plating Co
JHG PL Co

Assuming that you want to spell the word PL in full (i.e. Private Limited). If you are to find ("PL") and replace it with ("Private Limited"), you will replace the first & last company name correctly. At the same time, you will change the word "placement" to "Private Limitedacement" and the word "Gold-Plating" to "Gold-Private Limitedating", something you do not wish to do.

In most cases, people would have stopped here thinking that you have to do this manually. If you are one of them, read on.

PL is an isolate "word" in the listing while Placement and Gold-Plating are part of a word. So to effectively replace PL without touching Placement and Gold-Plating, you should find " PL " (note the spaces before and after) and replace it with " Private Limited " (note the spaces before and after). With this slight change, you would not have to waste time replace the word manually and save the time for other task.

You can also use this tip with any program that has a find and replace function.

Drop me a note if you think this is useful to you.

How to use the offset Function (Part IV)

From part III, you have learnt how to draw a dynamic Excel Chart. In this part, you will learn how to create a dynamic validation list using the same formula.

For those who don't know about data validation, it's purpose is to limit the type of data users could enter into a cell. If a data validation is a list, it will only allow users to select from the list.

Copy the formula "=OFFSET(A1,0,0,COUNTA($A$1:$A$100),1)" into the list as shown below. Click "OK" and you are done. Now, when you add or delete the listing in column A, the data validation list will get updated automatically.

How to use the Offset Function (Part III)

In this part, we will be sharing with you how to use the offset function to create dynamic ranges.

Before you could do that, you need to know 2 more functions from Excel. One is the COUNTA function and the other is NAMED RANGES.

COUNTA function counts the number of non blank cells in a pre-defined range. For example, if within a selected range from A1 to A100, the first 20 rows contains data, then counta will return 20 indicating that there are 20 rows that are non-blank cells. This COUNTA function will replace the height of the offset as shown =OFFSET(A1,0,0,COUNTA($A$1:$A$100),1). It makes the range dynamic and varies according to length of the list. In this case, we will be able to find out how many datasets are there in a chart data series.

After defining the formula that will vary according to the length of the list, you need to give it a name. Do this from the menu bar insert, name, define to create the range name. See the diagram below:



In the dialog box that follows, inout the name of the range (in this case called "chart_range") and input the formula "=OFFSET(A1,0,0,COUNTA($A$1:$A$100),1)" in the refers to box.



Then create the chart defining the range either as a x-axis or y-axis.

Once that is done, you have a dynamic chart that changes as more datasets are added or removed.