{"id":4502,"date":"2019-01-31T17:51:29","date_gmt":"2019-01-31T22:51:29","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4502"},"modified":"2019-01-31T18:12:20","modified_gmt":"2019-01-31T23:12:20","slug":"did-you-know-you-can-remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4502","title":{"rendered":"Did you know \u2026 you can remove duplicates in Excel?"},"content":{"rendered":"

I use Excel\u2019s COUNTIF<\/a> function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it\u2019s easy enough to get the list of IF\u2019s to count. But when I need to find the occurrence of different text strings, I need a unique list of<\/em> the strings first. \u201cRemove duplicates\u201d quickly exactly what I need.<\/p>\n

In this example, I have a list of all employees and contractor\u2019s departments and titles \u2013 I want to know how many people are in each department and how many people have each title. Removing duplicates modifies the data, so the first step is to make a copy of the spreadsheet. Highlight the data. Select \u201cData\u201d on the ribbon bar, then select \u201cRemove Duplicates\u201d<\/p>\n

<\/p>\n

Select the column(s) where you want to remove duplicate data. This could be exact duplicates across multiple columns (e.g. the unique \u201cCity, State\u201d combinations), or (in this case) I just want a unique list of departments. Click OK.<\/p>\n

<\/p>\n

A summary will be displayed showing you how many records were removed and how many unique values remain.<\/p>\n

<\/p>\n

Now that I have a complete listing of departments, I can use my COUNTIF function to show how many employees and contractors are in each department.<\/p>\n

<\/p>\n

Remove duplicates only deletes records within the highlighted data. Here, I have a list of all employee titles next to the department and count info we just created. If I highlight just<\/em> the \u2018Title\u2019 data and click \u201cRemove Duplicates\u201d, the department and count information is left unchanged.<\/p>\n

<\/p>\n

Now I have a unique list of titles as well.<\/p>\n

<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I use Excel\u2019s COUNTIF function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it\u2019s easy enough to get the list of IF\u2019s to count. But when I need to find the occurrence of different text strings, I need a unique list …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-4502","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4502","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4502"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4502\/revisions"}],"predecessor-version":[{"id":4509,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4502\/revisions\/4509"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}