Friday, 27 July 2012

How to Find Duplicates by Comparing Multiple Columns in Excel

Excel Match Function to Find Duplicates by Comparing one Column with another Column
By using IF and MATCH functions combination, you can find out the duplicates in a column by comparing it with another column.
Syntax:
=IF(ISNA(MATCH(LookupValue, LookupArray, MatchType)), False, True)
Here:
Ø  ISNA refers to IS Not Available means if the LookupValue is not available in the LookupArray, then the if condition returns the False statement first else returns the True statement.
For More reference about IF, MATCH Functions, Please search in Search Box available at the top of this Blog.
Remarks:
If you don’t use the ISNA in the formula then IF condition returns #N/A for False Statement

Example: 




Note:
If you have any ambiguity/if you want any another formula/method Please comment about it.

How to find Duplicates in a Particular Column or in a List of Data using Count IF Function

Excel CountIF Function to find Duplicates in Particular Column has a List of Data
We can find the duplicates in particular column using CountIF and IF functions combination.
Syntax:
=COUNTIF (Array, Value)
Here:
Array refers to the range of data where you want to find duplicates
Value refers to the value for which you test uniqueness.
Example:
Suppose Column A has a list  of data , in that if you want find the duplicates ,you have to write a formula in Column B as shown below.

=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique")

Excel MATCH Function Syntax and Example

Microsoft Excel MATCH Function
Returns the relative position of an item in an Array/Range of data that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
Syntax :
MATCH(LookupValue,LookupArray,MatchType)
Here:
LookupValue is the value you use to find the value you want in a table or given range of data.
LookupValue is the value you want to MATCH in LookupArray.
For example, when you look up an Employee ID number in a list, you are using the Employee name as the lookupValue, but the ID number is the value you want.
LookupValue can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
LookupArray  is a contiguous range of cells containing possible lookup values. LookupArray must be an array or an array reference.
MatchType is the number -1, 0, or 1. MatchType specifies how Microsoft Excel matches LookupValue with values in LookupArray.
      If  MatchType is 1, MATCH finds the largest value that is less than or equal to LookupValue. LookupArray must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
      If  MatchType is 0, MATCH finds the first value that is exactly equal to LookupValue. LookupArray can be in any order.
    If MatchType is -1, MATCH finds the smallest value that is greater than or equal to LookupValue. LookupArray must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
      If MatchType is omitted, it is assumed to be 1.
Example :
For example, If you want to look up the position of an Prod_Id(B7=789 which is LookupValue)then you can do by the following way.

=MATCH(F4,B1:B10,0) will returns the 7

Remarks:
MATCH returns the position of the matched value within LookupArray, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a MATCH, it returns the #N/A error value.
If MatchType is 0 and LookupValue is text, you can use the wildcard characters, question mark (?) and asterisk (*), in LookupValue. A question mark matches any single character; an asterisk MATCHes any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Thursday, 26 July 2012

Excel IF Condition Syntax and Example

Excel IF Conditional Function
IF is a conditional function, based on the given condition or logical test it written either TRUE statement(if the condition is true) or FALSE statement(if the condition fails).

Syntax:
=IF(Condition, TrueValue,FalseValue)
Where:
  • Ø  Condition is what you want to test (Eg: a=b, a<b, a<=b, a>=b,…).
  • Ø  TrueValue  refers to true statement.
  • Ø  FalsValue   refers to true statement
Example:
Suppose cell ‘A2’ has a value 10 and cell ‘B2’ has a value 12 . Now if you want test whether A2 is greater than B2 or not., and you want the True statement as ‘Greater’ and False statement as ‘lesser’, then you have to write the following formula in  Cell C2 as shown below.

=IF(A2>B2,”Greater”,”lesser”)
Here True and False statement should be in double quotes because they are strings.

VALUE_I
VALUE_I
IF CONDITION
Result
10
12
 =IF(A2>B2,"Greater","Lesser")
Lesser


Note:
In the above Formula in the example, IF condition fails so that it will written the result as “lesser”.

Thanks,
TAMATAM

Tuesday, 24 July 2012

Microsoft Excel LEFT Function Syntax and Example

MS Excel LEFT Function
LEFT returns the first character or characters from the left in a text string or number, based on the number of characters you specify.
Ø       LEFT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
Syntax:
=LEFT(String, Num_of_chars)
String is the text/number that contains the characters you want to extract from LEFT side of the string.
Num of chars   specifies the number of characters you want LEFT to extract.
Ø      Num_of_chars must be greater than or equal to zero.
Ø       If Num_of_chars is greater than the length of text, LEFT returns all of text.
Ø       If Num_of_chars is omitted, it is assumed to be 1 as shown below the screen shot.

Example:

STRING
LEFT FORMULA
RESULT
Ravi-Teja@Reddy
 =LEFT(A2,5)
Ravi-
Ravi-Teja@Reddy
 =LEFT(A3, LEN(A3)-FIND("-",A3))
Ravi-Teja@
123ABC#cde345
 =LEFT(A4, LEN(A4)-FIND("c",A4)+1)
123ABC
123ABC#cde346
 =LEFT(A5, LEN(A5)-SEARCH("c",A5)+1)
123ABC#c
Explanation :
=LEFT(A2,5)
This function returns the 5 characters from the left side of the string in A2.

=LEFT(A5, LEN(A5)-SEARCH("c",A5)+1)
Here in this case we find the No.of characters to extract with SEARCH Function.

Please be note that Find is a case sensitive function but Search is not.


Thanks,
TAMATAM



Excel CONCATENATE Function Syntax and Example

Excel CONCATENATE Function
With Excel Concatenate Function we can Join two or more strings into one string. You can also concatenate Text and Numbers
Syntax:
=CONCATENATE (string1,string2,...)

Str1, Str2, ...   are 2 to 255 items can be joined into a single item. The items can be text strings, numbers, or single-cell references.

Note:
You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join strings.
Example:


STRING_1
STRING_2
FORMULA
RESULT
Excel
Kingdom
 =CONCATENATE(A2,B2)
ExcelKingdom
Excel
Kingdom
 =A3&B3
ExcelKingdom
USER
123
 =CONCATENATE(A4,B4)
USER123
USER
123
 =A5&B5
USER123
1234
7890
 =CONCATENATE(A6,B6)
12347890
1234
7890
 =A7&B7
12347890
                                             
Thanks.,

TAMATAM


Sunday, 22 July 2012

Excel TRUNC Function Syntax and Example

Excel TRUNC Function
TRUNC is numerical function which truncates a number to an integer by removing the specified no.of digits in decimal, fraction portion.

Syntax:
=TRUNC (Value, No.of Digits)

Here:
VALUE which should be a number that you can truncate.
No. Of Digits refers to the size to truncate, if you don’t mention or specifies as zero this ,then the function will truncate entire decimal portion.

Examples:

NUMBER
No.of Digits
TRUNC  Function
Result
1123.6789

 =TRUNC(A3)
1123
1123.6789
1
 =TRUNC(A3,B3)
1123.6
1123.6789
3
 =TRUNC(A4,B4)
1123.678
1123.6789
2
 =TRUNC(A5,B5)
1123.67
1123.6789
0
 =TRUNC(A6,B6)
1123
TamaTam

 =TRUNC(A3)
#VALUE!


Thanks,
TAMATAM

Microsoft Excel INT Function Syntax and Example

INT Function:
The INT function rounds numbers down to the nearest integer. 

Syntax: 
=INT(number) 

Example:

NUMBER
INT Function
Result
23.67
 =INT(A2)
23
-23.67
 =INT(A3)
-24

Excel FLOOR Function Syntax and Example

FLOOR  Function:
The FLOOR function rounds a number down to the nearest specified multiple.
   
Syntax: 
=FLOOR (number,multiple) 

Examples:



NUMBER
Significance/Multiple
FLOOR Function
Result
10
4
 =FLOOR(A2,B2)
8
25.65
7
 =FLOOR(A3,B3)
21
-99
-10
 =FLOOR(A4,B4)
-90
25.65
-0.5
 =FLOOR(A5,B5)
#NUM!
-25.65
0.5
 =FLOOR(A5,B5)
-26

Explanation :


In above example "=Floor(10,4)" = 8 , because the nearest multiple of '4' which is less than the number '10' is 8.


Note: 

The number argument and the multiple argument must have the same sign or the function will return a #NUM! error. 

Excel CEILING Function Syntax and Example

CEILING Function :
The CEILING function rounds a number up to the nearest specified multiple. 

Syntax:

 =CEILING(number,multiple) 

Examples:



NUMBER
Significance/Multiple
CEILING Function
Result
10
4
 =CEILING(A2,B2)
12
25.65
7
 =CEILING(A3,B3)
28
-99
-10
 =CEILING(A4,B4)
-100
25.65
-0.5
 =CEILING(A5,B5)
#NUM!
-25.65
0.5
 =CEILING(A6,B6)
-25.5

Explanation :
In above example "=CEILING(10,4)" = 12 , because the nearest multiple of '4' which is greater than the number '10' is 12.

Note: 

The number argument and the multiple argument must have the same sign or the function will return a #NUM! error. 

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts