+ Reply to Thread
Results 1 to 7 of 7

Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

  1. #1
    Registered User
    Join Date
    02-06-2016
    Location
    Manassas, Virginia
    MS-Off Ver
    Office 360 Current version
    Posts
    4

    Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    Hello to all,

    I'm not the best a complex formula's and need some help

    I need a formula to mark files as duplicate while comparing multiple columns. I have attached an example.

    I have merged 2 different listings of songs. In column "A" I placed a number 1 to indicate the source. Column "B" is the number of the track. Column "C & D" are Title and Artist information. I need to mark the duplicate files as duplicate in Column "E".

    If column "B" and "C" are Equal then mark in column "E" duplicate but only mark the file that doesn't have the "1" in Column A. Then I can sort the duplicates and remove.

    In some cases I compare 2 columns and other times I compare 3 columns so I need to understand how the formula works so I can change it if I need to.

    Thanks for any and all help.

    Chuck
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    There are two parts to this: create what I call a "composite key" and matching the key to the range.

    The first part is creating the composite key. In this case you are interested in the combination of track and title. Concatenate them together. I normally put a delimiter between them so they are more human readable. So =B2&":"&C2, yields something like: 11180:I STILL BELIEVE. You can concatenate as many columns as you need to uniquely identify the record.

    The formula to identify whether a composite key is unique use the formula: =MATCH(E2,E:E,0)=ROW() - the way this formula works is it uses match to find the first occurrence of the item. Since we're using the whole column, this happens to be the row number. The ROW() part gets the current row number. So if the first time the formula finds the value is on the row with the formula, then it yields TRUE. If the value occurs on a later row, MATCH still finds the first occurrence and the formula yields false.

    Filter for TRUE and you have your unique list.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-06-2016
    Location
    Manassas, Virginia
    MS-Off Ver
    Office 360 Current version
    Posts
    4

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    This works great and thanks for the reply, however I need to consider Column A. Column A has the 1 or blank. I need to make false the rows that are duplicate in B & C but only if Column A has a 1. There are numbers in column B that not duplicate numbers and they are being marked True. M goal is to remove the rows that do not have information in B & C but only if the number in B is a duplicate.

    Sorry if I am being stupid about this but I really appreciate the help.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    In any communication breakdown, it can be the fault of the transmitter, the medium or the receiver so it is equally likely that I misunderstood the requirement.

    Let me restate what you just said in my own words to see if I got it now. If Column A does not have a 1, it is always true. If it has a 1 then do the check. In this case the formula should be: =IF(A1=1,MATCH(E1,E:E,0)=ROW(),TRUE).
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  5. #5
    Registered User
    Join Date
    02-06-2016
    Location
    Manassas, Virginia
    MS-Off Ver
    Office 360 Current version
    Posts
    4

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    As I try to understand how these formulas work they remind me of old dos logic commands. I am still not able to accomplish my goal and maybe it's not possible. I have uploaded one more example and created a column with the results I am trying to get by using some type of formula. Thank you for your patience and help.
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    I think I got it. It is always true if there is a 1. It is false if there is not a 1 and there is another occurrence of the value somewhere in the range. The first occurrence does not exempt it from being tested. This formula should do it: =IF(A3=1,TRUE,COUNTIF($B$3:$B$14,B3)=1).

  7. #7
    Registered User
    Join Date
    02-06-2016
    Location
    Manassas, Virginia
    MS-Off Ver
    Office 360 Current version
    Posts
    4

    Re: Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

    So the $B$3:$B$14 represents the entire list?

    So in my Workbook =IF(A1=1,TRUE,COUNTIF($B$1:$B$44697,B1)=1), Correct!!

    Looks like your at a Genus, I believe you've got it.

+ 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 Duplicate Entire Rows Keeping Row with Lowest Price
    By timmy1254 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-23-2015, 07:07 PM
  2. [SOLVED] Move data on duplicate rows (different columns) to one row and delete extra rows?
    By Sagwa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2014, 08:25 AM
  3. Merging Duplicate Rows and Keeping Original Data
    By vinnie179 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2014, 07:18 AM
  4. [SOLVED] how to delete delete rows with criteria and keeping formula
    By Bremmah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2013, 07:09 AM
  5. Delete duplicate rows with same data in certain columns for million rows
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-08-2012, 12:53 AM
  6. Delete duplicate rows of data
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2010, 01:45 PM
  7. Sum and delete Duplicate Rows VBA MACRO FORMULA
    By ARNO in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-11-2010, 11:56 AM
  8. Keeping duplicate rows
    By Daniell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2005, 02:06 AM

Tags for this Thread

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