{"id":6158,"date":"2022-09-01T20:23:15","date_gmt":"2022-09-01T14:53:15","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/"},"modified":"2022-09-01T20:23:15","modified_gmt":"2022-09-01T14:53:15","slug":"solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/","title":{"rendered":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-36612503\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"36612503\" data-parentid=\"36612287\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>So, going the VBA route, you&#8217;ll need to loop through each row in your tab and test the value to see if it&#8217;s a duplicate, if it&#8217;s a duplicate then you&#8217;ll increase a counter variable and once that counter variable hits 3 you start deleting rows. <\/p>\n<p>This is a little complicated if you haven&#8217;t worked with VBA. Please take some time to play with the code and understand it. I&#8217;ve written comments in it to help out.<\/p>\n<pre><code>Sub keepFirstThreeDuplicates()\n    Dim workingRow As Integer\n    Dim currentDup As String\n    Dim dupCounter As Integer\n    Dim wsheet As Worksheet\n\n    'change this to your tab name\n    Set wsheet = ThisWorkbook.Sheets(\"Sheet1\")\n\n    'loop through every row just guessing that your data starts at row 1 (A1) and goes to 50000 (A50000)\n    For workingRow = 1 To 50000\n\n        If workingRow = 1 Then 'we are at the first row, so grab the value and set dupCounter to 1\n            currentDup = wsheet.Cells(workingRow, 1).Value 'Assuming column 1, so this is Cell A1\n            dupCounter = 1\n        ElseIf currentDup = wsheet.Cells(workingRow, 1).Value Then 'we have another duplicate\n            If dupCounter = 3 Then 'We already have three duplicates, so delete the row, and set the row back one (because we deleted the row)\n                wsheet.Rows(workingRow).Delete\n                workingRow = workingRow - 1\n            Else\n                dupCounter = dupCounter + 1\n            End If\n        Else 'We are at a new value, so grab the value and set dupCounter to 1\n            currentDup = wsheet.Cells(workingRow, 1).Value\n            dupCounter = 1\n        End If\n\n        'exit the for loop if we hit a blank\n        If currentDup = \"\" Then Exit For\n    Next workingRow\n\nEnd Sub\n<\/code><\/pre>\n<p>If you are super new to VBA, to use this code:<\/p>\n<ol>\n<li>\n<p>While in your workbook, hit Ctrl+F11 to get to the Visual Basic<br \/>\nEditor (VBE)<\/p>\n<\/li>\n<li>\n<p>Your workbook will be called a &#8220;VBAProject&#8221; in the VBAProject panel.<br \/>\nRight click on it and select Insert&gt;&gt;Module<\/p>\n<\/li>\n<li>\n<p>Double click your new module &#8220;Module1&#8221; to open it. <\/p>\n<\/li>\n<li>\n<p>Paste in this code.<\/p>\n<\/li>\n<li>\n<p>To run it, click somewhere in the code and hit the play button up<br \/>\ntop (or F5 on your keyboard). Make sure you edit the code to suit<br \/>\nthe needs of your workbook like changing the tab name to your tab.<br \/>\nAlso make sure to back up your workbook before running it as this<br \/>\nwill delete rows and you won&#8217;t be able to undo.<\/p>\n<\/li>\n<\/ol>\n<p>Lastly, this is just one way to do it. It&#8217;s not meant to 100% address your needs since I&#8217;m guessing at number of rows, which column your data is in, that the data is already sorted by your duplicate column, and other whatnot. This is just to point you in a solid direction in VBA. It worked on my test workbook for a fake list I created in Column A of a fresh workbook. <\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Excel keep 1st three rows and delete rest of duplicates [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] So, going the VBA route, you&#8217;ll need to loop through each row in your tab and test the value to see if it&#8217;s a duplicate, if it&#8217;s a duplicate then you&#8217;ll increase a counter variable and once that counter variable hits 3 you start deleting rows. This is a little complicated if you haven&#8217;t &#8230; <a title=\"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\" aria-label=\"More on [Solved] Excel keep 1st three rows and delete rest of duplicates [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[1660,400,1612],"class_list":["post-6158","post","type-post","status-publish","format-standard","hentry","category-solved","tag-delete-row","tag-excel","tag-excel-2010"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] So, going the VBA route, you&#8217;ll need to loop through each row in your tab and test the value to see if it&#8217;s a duplicate, if it&#8217;s a duplicate then you&#8217;ll increase a counter variable and once that counter variable hits 3 you start deleting rows. This is a little complicated if you haven&#8217;t ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-01T14:53:15+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]\",\"datePublished\":\"2022-09-01T14:53:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\"},\"wordCount\":308,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"delete-row\",\"excel\",\"excel-2010\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\",\"name\":\"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-01T14:53:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb","og_description":"[ad_1] So, going the VBA route, you&#8217;ll need to loop through each row in your tab and test the value to see if it&#8217;s a duplicate, if it&#8217;s a duplicate then you&#8217;ll increase a counter variable and once that counter variable hits 3 you start deleting rows. This is a little complicated if you haven&#8217;t ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/","og_site_name":"JassWeb","article_published_time":"2022-09-01T14:53:15+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]","datePublished":"2022-09-01T14:53:15+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/"},"wordCount":308,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["delete-row","excel","excel-2010"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/","name":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-01T14:53:15+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-keep-1st-three-rows-and-delete-rest-of-duplicates-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/6158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=6158"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/6158\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=6158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=6158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=6158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}