Welcome to Knowledge and fun blog. For this section of Knowledge and fun blog is about how to use MS Excel to help your work easier.
Good afternoon Saturday. This post will share how to check duplication in a column. According to the previous posts "Excel - Merging columns", this post will using the same situation that you are a collector for your company new year guest list. After you put all list of guest first name and last name together in one sheet, of course you will get big number of guest like up to a hundred. How do you know whether there are any duplicate guest name otherwise you will send them the duplication of invitation letter or prepare wrong budget for the party. Note: to make this duplication check effective you better store "First name" and "Last name" in separate column follow "Excel-Separate text in one column to two columns". Other wise, you will get an incorrect result of duplication check because this formula will consider a space as one character so if First name and Last name are stored in one cell and there are 2 same person but different number of space between First name and Last name this formula will consider as different person. However lower case or upper case is doesn't matter.
The picture below show incorrect duplication check if same person but got different space between first name and last name.
Note:1. TRUE= There is a duplication of this name | FALSE=There is no duplication of this name
2. If you do sorting before checking duplication, it will help you easier to find the duplication name.
Good afternoon Saturday. This post will share how to check duplication in a column. According to the previous posts "Excel - Merging columns", this post will using the same situation that you are a collector for your company new year guest list. After you put all list of guest first name and last name together in one sheet, of course you will get big number of guest like up to a hundred. How do you know whether there are any duplicate guest name otherwise you will send them the duplication of invitation letter or prepare wrong budget for the party. Note: to make this duplication check effective you better store "First name" and "Last name" in separate column follow "Excel-Separate text in one column to two columns". Other wise, you will get an incorrect result of duplication check because this formula will consider a space as one character so if First name and Last name are stored in one cell and there are 2 same person but different number of space between First name and Last name this formula will consider as different person. However lower case or upper case is doesn't matter.
The picture below show incorrect duplication check if same person but got different space between first name and last name.
Note:1. TRUE= There is a duplication of this name | FALSE=There is no duplication of this name
2. If you do sorting before checking duplication, it will help you easier to find the duplication name.
Now, please let me start the instruction.
1. Once you get all list of your guest name (First name and Last name) in the same column. Then, prepare the right hand side columns for the result of "Duplication check". For the example below, I use "G" and "H" column for the duplication check result.
2. Next in cell "G5" type "=countif(D:D,D5)>1", you will see that there is a highlighting automatically cover D column and a rectangle cover cell D5. Then hit "Enter". This means Excel will mapping value in "D5" which is "Bernard" to every value in every cell on D column. If there is any duplication just show TRUE on cell "G5", unless show FALSE.
3. Let do the same thing with cell "H5" but since H column will show result of Last name duplication check which is on E column, then we type "=countif(e:e,d5)>1" and hit "Enter".
4. Now, copy cell "G5" and "H5" and paste to cell "G6" till "H18".
5. You will see the checking result like below picture.
6. Now, you need to analyze the result. There are 4 cases below that you need to consider.
- Case 1 FALSE | FALSE = No duplication.
- Case 2 TRUE | TRUE = Has a duplication.
- Case 3 FALSE | TRUE = No duplication. (someone may use same last name).
- Case 4 TRUE | FALSE = No duplication. (someone may use same first name).
Please note: If you do the sorting before check duplication (sort by First name), it do help you a lot. Because name that similar will be stand close together. It is easier for you to find out whether which name are duplicate.
Watch VDO..
Good night and thanks for following my blog.
Other Excel lessons please go here "Excel makes your work easy"
|
