"&" Ampersand vs Concatenate function in joining Cell Content
In this Blog I will show you how a simple character "&" called ampersand can replace the Concatenate Function of Excel and Google Sheets.
The Ampersand technique is the Faster way of joining Cell content instead of using Concatenate Function
Course 4 Process Data from Dirty to Clean, Week 2, Video on Optimize Data-Cleaning Process on Google Analytics, talks on the use of Concatenate function to join one cell data to another cell data.
In this case we have the following scenario:
Reference File:
Course 04_Week 02_Lesson 13_Cleaning data with Spreadsheets_Cosmetics INc - Data for Cleaning
|
|
H
|
I
|
K
|
|
1
|
Product ID
|
Code
|
Product Code
|
|
2
|
51993
|
Masc
|
51993Masc
|
|
3
|
49631
|
Foun
|
49631Foun
|
|
4
|
42292
|
Glos
|
42292Glos
|
|
5
|
86661
|
Shad
|
86661Shad
|
|
6
|
49541
|
Eyel
|
49541Eyel
|
|
7
|
58337
|
Foun
|
58337Foun
|
H2 data : 51993
I2 data : Masc
To join the content of H2 and I2 Concatenate function is used as per video instruction.
K2 formula : =Concatenate(H2,I2)
K2 result : 51993Masc
To use the function concatenate is longer to type. The best and faster alternative is to use the shortcut method, just use the ampersand "&" to join one cell to another. Here is the the example which creates the same result.
K2 formula : = H2&I2
K2 result : 51993Masc
Hope this trick will help you well with your data analytics venture.
Cheers!
CountIf Formula
I got a call from the Cost Reduction team how to set-up an auto-count for their tally. Here is the scenario:
In the tally field column you have to write the formula that is written in column L.
How does it works?
=COUNTIF(Source Range for the Count,Cell containing the Criteria)
In Cell L5 for example Type "A" tallies to 4. It's Source Range is H5:H10 and it's criteria is K5 which contains Type "A".
Microsoft Excel provides this function that can be used to analyze your data based on a condition. For example, to calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
Excel Downloads
These are collections of analytical tools using Excel.
URL: http://www.exceluser.com/help/download/long002.htm
Cool Analog Clock in VBA
Here is a very good source in creating fancy analog clock with VBA and excel chart.
URL1: http://www.j-walk.com/ss/excel/files/clockchart.htm
URL2: http://www.andypope.info/fun/clocks.htm
Overview
This workbook contains an XY chart that displays a working analog clock.
It uses VBA to update the clock every second. This is really not a
"practical" application. Rather, it's an interesting charting demonstration
that uses VBA. The VBA project is not protected, so you can view the code.
Free Download
Click the link below to get a copy of this file. It works with Excel
97 or later.
Adding your own toolbars
| Applies to |
|---|
| Microsoft Excel 2000 |
If you don't want to modify the existing toolbars, you can add a new toolbar and then customize it with the buttons and commands you use most frequently.
Creating a new toolbar
To create your own toolbar:
- Click the Toolbars tab on the Customize dialog box.
- Click the New button, and then type a name for the toolbar.
- Click the Commands tab, and drag buttons and commands to the toolbar.
Note Adding commands to your own toolbar doesn't remove them from the existing toolbar where they normally appear.
Once created, you can display and hide your custom toolbar as you do any toolbar: right-click any toolbar or the menu bar, and then select or clear the check box for your custom toolbar.
Excel 2000 saves your custom toolbar, and any other toolbar and menu changes you've made, when you exit from Excel. Excel saves toolbar settings in file Excel.xlb, located in your user profile folder. For the location of this folder, type user profile folder in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.
Tip You can create and open different versions of this file to create more than one toolbar configuration or share a custom toolbar with other users. For more information, see How to use custom toolbars from previous versions of Excel.
Creating buttons for your macros and hyperlinks
If you use macros to automate tasks that you perform frequently, you can create your own toolbar buttons to run the macros. You can also create buttons that provide hyperlinks to files and Web pages that you use frequently.
Recording macros
For information about recording macros, type record a macro in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. If you want your macro button to be available whenever you use Excel, make sure you store it in the location recommended in the procedure.
Add a new button, or create your own
- On the Tools menu, click Customize.
- Click the Commands tab, click Macros, and drag the Custom Button item to the toolbar where you want a macro or hyperlink button.
More info:
http://office.microsoft.com/en-us/assistance/HA010548161033.aspx
AutoFilterMode Property
True if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property. Read/write Boolean.
Remarks
This property returns True if the drop-down arrows are currently displayed. You can set this property to False to remove the arrows, but you cannot set it to True. Use the AutoFilter method to filter a list and display the drop-down arrows.
This example displays the current status of the AutoFilterMode property on Sheet1.
Sub TestFilter()
If Worksheets("Sheet1").AutoFilterMode Then
isOn = "On"
Else
isOn = "Off"
End If
MsgBox "AutoFilterMode is " & isOn
End Sub
For more info. visit Microsoft MSDN Section.
