+ Reply to Thread
Results 1 to 6 of 6

Remove rows, based on duplicates in a column and another variable in different column???

  1. #1
    Registered User
    Join Date
    07-09-2008
    Location
    Ireland
    Posts
    22

    Remove rows, based on duplicates in a column and another variable in different column???

    Hi all,

    Can anyone help me with a formula for this please????

    I think the best way to explain is with a sample file. But what I am trying to do is: delete rows where there is a duplicate value in Column B, but only a duplicate when column A is the same value. In the sample file, I highlighted the cells that I want to delete in red. The Results worksheet is what finished product should look like. The real file has 40 columns and 7000 rows.
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Remove rows, based on duplicates in a column and another variable in different column?

    Hi,

    Would've thought VBA would be a better option, but anyway, place this formula in G2:

    =AND(ROWS($1:1)<>MATCH(A2&"|"&B2,INDEX($A$2:$A$25&"|"&$B$2:$B$25,,),0),COUNTIFS($A$2:$A$25,A2,$B$2:$B$25,B2)>1)

    Copy down as required.

    Filter on this new column for TRUE. Delete rows.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-09-2008
    Location
    Ireland
    Posts
    22

    Re: Remove rows, based on duplicates in a column and another variable in different column?

    Thanks XOR LX,

    I have no objections to VBA - if you think it is a better idea. The formula seems to work fine for me - but it freezes my application when I change it.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Remove rows, based on duplicates in a column and another variable in different column?

    Is this a one-off operation, or something you need to be able to call upon again in future?

    What do you mean by "when I change it"? Did the formula finish calculating? Did you delete the rows? When exactly did it "freeze"?

    Regards

  5. #5
    Registered User
    Join Date
    07-09-2008
    Location
    Ireland
    Posts
    22

    Re: Remove rows, based on duplicates in a column and another variable in different column?

    It is something I will do regularly. It completed after approx. 15 mins of processing when I dragged it down along the 7000 rows. Later, I sorted Column A alphabetically, and it had to reprocess - it's been running approx. 5 mins so far....

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Remove rows, based on duplicates in a column and another variable in different column?

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.
    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete rows where column A has duplicates based on values in Column B
    By rajakumar.g@tcs.com in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2013, 08:45 AM
  2. Remove rows with duplicate values in one column based on value of another column
    By jolleyje in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2013, 06:20 AM
  3. [SOLVED] Remove rows based on duplicates in certain columns, and merge data from another column
    By yanagi600 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-02-2013, 04:17 PM
  4. Remove duplicates on column A and merge rows in column B
    By JJcool in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2012, 05:41 PM
  5. remove entire row based on duplicates from single column
    By mikemeadeuk in forum Excel General
    Replies: 2
    Last Post: 10-15-2011, 02:57 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1