Learn Excel Macro Training with Free Lessons & Tips

Concatenate , one of the text functions, to join two or more text strings into one string.

The Excel Concatenate function joins together a series of supplied text strings or other values, into one combined text string.

The syntax of the function is:

CONCATENATE( text1, [text2], ... )

Where the text arguments are a set of one or more text strings or other values that you want to join together.

Note that:

• In current versions of Excel,  you can supply up to 255 text arguments to the Concatenate function, but in Excel 2003, the function can only accept up to 30 text arguments.
• In Excel 2016, the Concatenate function has been replaced by the Concat function. However, the Concatenate function is still available in Excel 2016, to ensure compatibility with earlier versions of Excel.

Purpose:

This function calculates difference between two dates. it can show the result in days, months and years.

Syntax:

=datedif(start_date,end_date,"unit")

Example

Remarks:

• Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• The DATEDIF function is useful in formulas where you need to calculate an age.

In Column “C”, I have product list and some of them are duplicates. My requirement is to format with background color of green for 1st found value among the duplicates. To solve this, I used formula in Conditional formatting. Check the below formula & screenshot.

Formula in Conditional Formatting is: =COUNTIFS($C$2:C2,C2)=1

Note: In the above formula if you write 2 instead of 1 then it will highlight 2nd duplicate value.

Binding is a process of matching function calls written by the programmer to the actual code (internal or external) that implements the function. All functions called in code must be bound before the code can be executed.

Early binding is a process of binding matching function calls written by the programmer to the actual code that implements the function at compile time

Late binding is a process of binding matching function calls written by the programmer to the actual code that implements the function at run time

Early Binding benefits:
    The use of Intellisense,
    The use of constants,
    Use of the Object Browser,
    Help using F1.

Late Binding benefits:
    Does not require declaring reference libraries.
    Does not suffer from versioning issues.

Drawbacks of Late Binding:
    Loss of Intellisense during development.
    No constants.
    Late binding is slower than early binding.

There are two types of array in VBA, Static array and Dynamic array.

Static array:

• Array is dimenstioned during design time.
• Array size can not be changed.
• By using erase function, only array element values in the memory will be emptied, elements memory will not be deleted.
• Memory is fixed during design time.

Dynamic array:

• Array is dimenstioned during runtime
• Array size can be changed (Shrink or Enlarge) any number of times.
• By using erase function, both array element values and elements memory will be deleted.
• We can utilise the memory efficiently.

VBA (Excel VBA, Access VBA, Word VBA, Outllook VBA, Power Point VBA, MS Project VBA) defined two types of string data type, Fixed string and Variable string.

Fixed String:

• Length is fixed, data beyond that limit will be truncated.
• Memory will be blank, in case of number of character is less than the maximum length.

Variable String:

• Length will be varied depends on the data length.
• Allocated only as much as memory based on the data length.

Hi,

In today's Excel topic we will see how Index & Match function is superior than Vlookup/Hlookup.

With Index and Match, the return value need not be in the same column as the lookup column, unlike VLOOKUP where the return value has to be in the specified range. How does this matter? With VLOOKUP, you have to know the column number that contains the return value. While this sounds like no big deal, it can be cumbersome when you have a large table and have to count the number of columns. Also, if you were to add/remove a in your table, you have to recount and update the col_index_num argument. With Index and Match, no counting is required as the lookup column is different from the column that has the return value.

With Index and Match, you can specify either a row or a column in an array or even specify both. This means you can look up values both vertically and horizontally.

Index and Match can be used to lookup values in any column. Unlike VLOOKUP where you can only look up to a value in the first column in a table, Index and Match will work if your lookup value is in the first column, the last, or anywhere in between.

Index and Match offers the flexibility of making dynamic reference to the column which contains the return value. What this means is that you can add columns to your table and INDEX and MATCH will not break. On the other hand, VLOOKUP breaks if you had to add a column to the table as it makes a static reference to the table.

In excel there are two functions which are taking same arguments and return type.

SEARCH() --> Search a text within text, Case insensitive

• Example: Search("t","Management Team")  and Search("T","Management Team") both will return same position i.e. 10

FIND() --> Find a text within text, Case sensitive i.e. Search("t","Management Team")  and Search("T","Management Team") both will return same position ie

• Example: Search("t","Management Team")  and Search("T","Management Team") both will return differenct position i.e.

• Find("t","Management Team")  will return 10
• Find("T","Management Team") will return 12

Same functions can be used in excel vba macro also using WorksheetFunction.Search and WorksheetFunction.Find

Generally, we are only use maximum "30 formulas" in our day to day routine. let's explore with below formulas and let me know which one is your favorite.

1. Vlookup
2. If
3. Iferror
4. Sum
5. Count
6. Sumif
7. Countif
8. CountA
9. Index
10. Match
11. Left
12. Right
13. Mid
14. Trim
15. Clear
16. Len
17. Iserror
18. Subtotal
19. Sumproduct
20. Search
21. Find
22. Proper
23. Upper
24. Lower
25. Sumifs
26. Countifs
27. Max
28. Min
29. Choose
30. Hlookup

Thanks