Separate a string of values into different columns (Part III)

Spliting strings of values which are separated by a symbol is a breeze using the text to column function. What happens when you have a string without any separator, like those given below?

tops278484092828

name732180244063

baby276540921867

You can use the fixed width option instead of the delimited option in "Text to Column" function. For those who are using Excel 95 or 97 or evenExcel 2000, you might need to convert the font to one that is fixed width. This means that a "M" will occupy the same width as an "I". The font type I recommend is "Courier" or "Courier New".

Here are the steps:

1) Highlight the rows you want to parse.

2) Change the font to Courier.

3) Goto menu, Data, Text to Column

4) In step 1 of the wizard, select the option "fixed width". Click Next

5) Point the mouse pointer at the appropriate width and click the left mouse button once.

6) If you want to remove any of the preset separator, click the left mouse button at the appropriate black line or separator twice.

7) Once you have set the relevant width, click finish and the strings of value are separated into the respective columns.

Separate a string of values into different columns (Part II)

We found a way to split the values into the respective columns if they are separated by commas. What happens if they are separated not by commas but by the sign \ or ! or ~ ....... Is it possible then to separate them? The answer is yes. If we have a set of values which are separated by "" like those below , we can also split them up into the respective columns.

top 100 baby names\2784840\92828

baby names\732180\24406
baby\27654\09218

This is how it should be done.

1) Highlight the rows you want to break up.

2) On the menu, select data, text to column.

3) In Step 1 of the wizard, make sure the option "delimited" is selected.

4) Instead of checking on the comma delimiters box as demonstrated yesterday, check on the box labelled others and input the sign "\" into the empty box that follows. Once you do that, the values are separated by black lines.

5) Click on finish and you are done.

Separate a string of values into different columns

Imagine you have a Comma Separated Values file (CSV in short). When you open the file, Excel fails to separate the values for you. No matter how many times you re-open the file, it remains as follows:

top 100 baby names,2784840,92828
baby names,732180,24406
baby,276540,9218

Are you going to separate them manually? On the above example, there are only 3 sets of values. What if there are 100 rows, 200 rows or more.

In Lotus 1-2-3, the function to separate the values is called parsing. In Excel, you can do the same through the function called "Text to Column".

1) Highlight the rows you want to break up.
2) On the menu, select data, text to column.
3) In Step 1 of the wizard, make sure the option "delimited" is selected. Click next.
4) Check on the comma delimiters box. Once you do that, the values are separated by black lines.
5) Click on finish and you are done.

The values are now separated into the respective columns.

Calculating Working Days

Excel contains a function that helps you calculate the number of working days between 2 dates. It is called Networkdays. The function assumes a 5 days work week. To use the function, you need to activate an add-in called analysis toolpak. You can do this by going to the menu, tools, add-in and check on the box labelled "Analysis Toolpak". You may require Microsoft Office installation disk to activate the add-in.

The function also allows you to add in holidays such that they are excluded if these holidays fall on weekday and are within the period for the calculation.

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

We have covered the first formula last week, what about this second formula
=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))?

Before we share with you how the different functions are applied, do take note that the function starts with Sum(if and not sumif. There is a bracket in between the sum and the if. This denotes that it is a multi-conditional sum. To activate the formula, you need to use Ctrl + Shift + Enter together.

What are the conditions in the multi-conditions sum? Let's break them up and see what they meant

Click here for diagram


The formula Len(B1:B8)>0 is used to determine that the cell in the range is not a blank. If there is a character in the cell, then the length is at least 1 and it will return true as shown.

The formula shown in column D is developed to indicate the first row that the content shows up. For example, Internet marketing in row 6 returns a value of 3 because the phrase "internet marketing" shows up the very first time in Row 3.

In Column E, the formula frequency will calculate the number of times the number 1 shows up in the list in column D, followed by 2, 3, ..etc. Notice the simlarity in this formula with the complete formula =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)) In fact, the range D1:D8 as shown in the simplified formula is replaced by the formula show in prior column D. The replacement moves us closer to the complete formula. For clarity, the simlarities are highlighted in blue and green colour. Different colours are used to show that the range D1:D8 are replaced twice.

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

In the final part shown in column F, the formula =if(E5>0,1,0) is used to pass instruction to Excel to put in the number 1 in those rows in column F where the numbers showed up in column E is more than 0. By putting the conditional sum in the formula, we have completed the entire formula where the end result shown is the number of unique records in the list. Do remember to use Ctrl + Shift + Enter together to effect the formula.

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.