Kawser
You may have some data and you want to check this data against another available list.
Download this file to work with us in the following example.
See the following screenshot of our example. We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.
Matching text in a list in Excel. The goal is to identify member numbers that are in the resigned members list in column F.
If you want to grasp this example very well, you have to be an expert on the following topics.
The above screenshot shows a simple example. The data is in the range A5: C133. The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column F. You can delete these unnecessary rows later.
Here’s a formula entered in cell D5, and copied down to cells where Names are available.
=IF(COUNTIF($F$2:$F$22,B2)>0,”Resigned”,”” ), the bold part of the formula will return 1 if it (bold part) matches a list value with a data value. If the BOLD part returns 1 or more, then the IF function will return “Resigned”, otherwise nothing.
This whole formula will display the word “Resigned” if the “Member Num” in column B is found in the “Resigned Members” list. If the Member number is not found, it returns an empty string. You can sort the list by column D, the rows for all resigned members will appear together and can be quickly deleted. To sort by column D, just select from cell D5 to D133 and then choose Home⇒Editing⇒Sort & Filter ⇒ Sort Z to A.
Our Excel sample file will be like this one:
Matching text in a list using Excel.
This technique can be adapted to other types of list-matching tasks.
Read More…
Data clean-up techniques in Excel: Replacing or removing text in cells
Data clean-up techniques in Excel: Changing vertical data to horizontal data
Data clean-up techniques in Excel: Extracting a filename from a URL
Happy Excelling 🙂
