To validate some of my skills, I am studying for the Excel MOS exam. Since I work in data processing a lot, it seemed like a good time to document some features in Excel that may not necessarily be that apparent to the casual Excel user. First up - remove duplicate records in Excel.
Let's start with the sample Financial Excel sheet from Microsoft. Its available here:
The sheet has 701 rows in it (including the header row), and I am going to use it for other posts as well. But first, for our purposes, we'll copy the first four rows of data at the top, and paste them into the bottom of the sheet.
Then select all 705 rows, including the header.
Click on the DATA tab in the menu bar, and then click the Advanced option in the Sort and Filter section.
The Advanced Filter window will appear. At the top is an Action option. Leave it set as Filter the list, in place. The rows you have selected will be listed in the List Range field. if you only wanted to filter based on a specific column, you could do that by selecting it as part of the Criteria range field. For our purposes, we will leave that blank, since we want to make sure all fields are unique. Finally, check the Unique records only box. The box should look like the one below.
Now click OK. The Excel sheet will update, and you'll see that the duplicated rows are removed. Very handy when bringing records into a database or an analytics process.
Comments
Post a Comment