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.