Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Macro to delete row with duplicate cells


  • Please log in to reply
5 replies to this topic

#1 stantech

stantech

  • Members
  • 50 posts
  • OFFLINE
  •  
  • Local time:01:58 PM

Posted 08 April 2012 - 09:38 AM

Does anyone have a macro that will delete row that have duplicate cells in same row other columns?

BC AdBot (Login to Remove)

 


#2 James Litten

James Litten

    Ԁǝǝ˥q


  • BC Advisor
  • 1,946 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New Jersey
  • Local time:01:58 PM

Posted 08 April 2012 - 11:41 AM

Hi

Are you looking to delete rows that have duplicate data but the duplicate data is in different columns?
Can you give a small example?

If the duplicate values are in the same column then you can use the 'remove duplicates' tool.
Select the rows/columns then go to DATA and you should see the REMOVE DUPLICATES tool.

James

#3 stantech

stantech
  • Topic Starter

  • Members
  • 50 posts
  • OFFLINE
  •  
  • Local time:01:58 PM

Posted 08 April 2012 - 07:14 PM

I am looking for duplicate cells in other columns of the same row. If that is the case then delete row
Example:
Col A ColB
Row1 2 1
Row2 1 1
Row3 3 1

Then delete Row 2

#4 James Litten

James Litten

    Ԁǝǝ˥q


  • BC Advisor
  • 1,946 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New Jersey
  • Local time:01:58 PM

Posted 08 April 2012 - 08:54 PM

Oh, okay.

I assume you can write the macro but are just looking for an efficient way to find duplicates in a row in Excel. It is tricky but I've had to do it before and what I do is look at how many unique values are in the row and if the number of uniques are less than the number of columns then I know there are duplicates in the row. Here is the formula I use when none of the cells are blank or contain text...

=SUM(IF(FREQUENCY(A1:B1,A1:B1)>0,1))

For your example if you start your values in A1 then put that in C1 and copy C1 then paste it in C2 and C3 and you will see that it shows 2 for rows 1 and 3 and 1 for row 2 because it only has one unique and therefore since 1 is less than the number of columns (2) you would delete it.

Here's a screenshot of using it with 6 columns. Any column where the formula results in a number less than 6 has duplicates in the row and you would code it to be deleted in your macro.

G1 would contain the formula =SUM(IF(FREQUENCY(A1:F1,A1:F1)>0,1)) copy G1 and paste it into G2 thru G5 ...

Posted Image

James

Edited by NeverSayDie, 08 April 2012 - 08:56 PM.


#5 stantech

stantech
  • Topic Starter

  • Members
  • 50 posts
  • OFFLINE
  •  
  • Local time:01:58 PM

Posted 09 April 2012 - 04:58 AM

James
I understand the formula but I do not know how to write the macro. Appreciate some help there if possible. Thanks.
bob

#6 James Litten

James Litten

    Ԁǝǝ˥q


  • BC Advisor
  • 1,946 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New Jersey
  • Local time:01:58 PM

Posted 09 April 2012 - 09:11 AM

I can't help you with a macro. Maybe someone else can.

James




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users