Results 1 to 5 of 5

Formula to delete every second row

This is a discussion on Formula to delete every second row within the Excel Questions forums, part of the Question Forums category; Hi there, I have a spreadsheet with duplicate data on every second row, just wondering if anyone knows of a ...

  1. #1
    New Member
    Join Date
    Jun 2008
    Posts
    2

    Default Formula to delete every second row

    Hi there,

    I have a spreadsheet with duplicate data on every second row, just wondering if anyone knows of a formula that will delete every second row...

    Thanks!

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Location
    Melbourne, Australia
    Posts
    123

    Default Re: Formula to delete every second row

    Well formulas dont delete so if you are just looking for an easy way to identify what you need to delete, add a formula to an empty column that says
    =A1=A2
    Then copy the formula down the length of the data.
    Every duplicate row will return TRUE.
    Then using a data filter, you can filter and delete all the TRUEs.
    ---------------------------
    Yes I know there are better ways to do it.
    My brain is a Double. Add in too many values and it Overflows.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Location
    seattle
    Posts
    394

    Default Re: Formula to delete every second row

    If your data is unsorted:

    If column A has the unique identifier:

    enter this formula into row one in an available column, and drag down all of the rows:
    =COUNTIF($A$1:A1,A1)

    That will number each record (first occurance of a dupe will have a 1, second occurance will have a 2, and so on)

    Copy all of the formulas that you just entered then Paste Special... Values
    Now you can sort on that column and delete all rows that have a number greater than 1
    I want Excel and Access to get married and have a baby. And I would call that baby 'Accel'

    Windows 7, Office 2010

  4. #4
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    2,826

    Default Re: Formula to delete every second row

    Highlight it, apply an advanced filter, choose Copy to another location, choose where to copy it and tick Unique records only.
    .select, overused and inefficient.

  5. #5
    New Member
    Join Date
    Jun 2008
    Posts
    2

    Default Re: Formula to delete every second row

    THANK YOU THANK YOU THANK YOU!!

    You have saved me lots of time!

    Very much appreciated!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com