Removing Duplicates With Criteria

Feb 6, 2007

I'm adding data from a report into a spread sheet and some of it will be duplicated. I want to remove the duplicate data, but is there a way to differentiate between the older (and more complete) data and the newer data? In other words, how do I get rid of the duplicate while keeping the one I want to keep?

I was considering the advanced filter, but if I create too many columns of criteria will it be seen as unique?

View 9 Replies


Removing Duplicates Based On Column Values And Criteria?

Apr 3, 2013

removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.

Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.

I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria

I would need an excel formula not macro

View 9 Replies View Related

Removing Some Duplicates

Sep 12, 2012

I am trying to organize some meteorological data for a project and I ran into a wall, basically I have 3 columns in one there is the date, in one the hour and in the third one the temperature the issue is that in the hour section i have the hour 12:00 that repeats its self , and this goes for the hole year , pretty much every day i have the hour 12:00 that repeats twice , so select for every Monday , Tuesday etc only certain hours.

View 5 Replies View Related

Removing Duplicates..

Oct 21, 2009

I have list of data references about 60000 of them but some are duplicated. I have used advanced filted then unique records only. So now i have just the unique records showing now. How do i copy the accounts that is just unique into a new worksheet? I tried copying it but its copying everything. I even tried using paste value but still copying everything?

View 4 Replies View Related

Removing Duplicates

Apr 17, 2008

I have a spreadsheet which contains 2 columns of data, most of which are duplicates.

I'm looking for a macro which will check all of colum A (A2:A138)


Column B (B2:B163)

I would like the macro to remove duplicate entries (from column A) in column B so that all that is left in column B are entries which don't match any in column A

View 9 Replies View Related

Removing Duplicates With Less Elements In A Row

Nov 22, 2011

Writing macro for "Removing duplicates with less elements in a row."

I am trying to remove duplicate entries from a file based on the number of data present in a row. Please see below

Name Col-1 Col-2 Col-3 Col-4
abc 1 3 4
abc 1 2 3 4
abc 1 4

I want to retain the row with maximum elements(row 2 in above eg.).

Result should be:

Name Col-1 Col-2 Col-3 Col-4
abc 1 2 3 4

Currently I am doing this manually, by adding countA at the end of each row, then arranging them in descending order. That will make sure that the row with more data comes first n hence gets retained, while other rows gets deleted.
Can this be done using Macro?

Below macro just deletes the rows,

Public Sub DeleteDuplicateRows()
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False

[Code] .....

View 5 Replies View Related

Removing Duplicates From A List Of Data

Apr 3, 2014

I have data in the form of a table. For example a list of duplicate names, each of these names have a corresponding date (lease expiry date). The problem is that the duplicate names have varying dates. So the remove duplicates function does not work because i need to remove the duplicate names with the older dates. I want the latest dates to remain behind.

Data Currently: Solution should be:

View 1 Replies View Related

Excel Function Removing Even Duplicates

May 7, 2014

I want to pull the very last odd duplicate. Example below, I want to pull out A3, and C5 and delete the rest. Is there a function that will allow me to do this?

For example,

Column 1 Column 2
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3
C 4
C 5
D 1
D 2
D 3
D 4

View 1 Replies View Related

Rule For Removing Duplicates In Data?

Jun 10, 2014

I'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.

I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?

View 3 Replies View Related

Removing Duplicates Feature Is Malfunctioning

Mar 13, 2009

i'm trying to remove duplicates from a worksheet containing customer contact info. the sheet has 9 columns with headings, and the duplicates appear in the last name and phone number column. (the sheet contains no outlines/groups/subtotals.)

i want to remove entries that contain the same last name AND phone number, however when i go to DATA>DATA TOOLS>REMOVE DUPLICATES, and specify the columns i want to remove duplicates from, it keeps deleting an entry that has the same last name, but not the same phone number.

i even tried removing duplicates from only the phone number column, and it still removes the phone number for the entry that has a duplicated last name, even though the phone numbers are different.

View 3 Replies View Related

Locate Duplicates Within Worksheet Before Removing

Mar 13, 2013

I have about 20k records with dealer codes and brands listed. I need to be able to see the duplicates from the dealer numbers and brands. Is there a formula that can be used to locate them and see them before removing them?

View 3 Replies View Related

Removing Duplicates Based On Several Columns

Aug 13, 2013

I have a list of about 85,000 addresses and I know that there are about 35,000 duplicates in it.

If I do 'Remove duplicates' it deletes them but takes the first one of each it finds and what I want to do is remove the duplicate with no UPRN in it (Column B)

View 6 Replies View Related

Excel 2007 :: Removing Duplicates In Row?

Oct 12, 2011

Can someone look at the sheet sample? I am trying to turn duplicates into a zero like Ive done in record 1 so that the same tax bill is not counted twice. The records are in rows and if I do a transpose and try to do it by hand it will take forever cause I have hundreds of records.

Excel 2007
BQBRBSBTBUBVBWBXBYBZCACBCCCDCE1txt_nme_taxpayertxt_nme_streetcity state txt_addr_1txt_parcel
Year 10Year 9Year 8Year 7Year 6Year 5Year 4Year 3Year 2Year 12QUINCY'S REALTY INC % RASH & A19900
HOLIDAY LNCHARLOTTE, NCPO BOX 16003192170500.55516.22712.71768.860000780.713FERGUSON, BENJAMIN10733
MONTECARLO DRCHARLOTTE, NC3508 SARGEANT DR151840720.4423.5180.381.988.2387.689.1789.1789.1789.174HARVEY,

[Code] ........

View 2 Replies View Related

Removing Duplicates From Macro Recording?

Feb 20, 2012

I incorporated more codes to the ones that were just solved from this board, but how can I make the active cell stay on A1 of sheet "hypo_tax_dropdown"? Also, I obtained the codes for removing the duplicates from macro recording, will these codes work on any machine? I noticed that it doesn't have worksheet.function

Sub Macro1()
Dim X As Long


View 7 Replies View Related

Removing Duplicates Without Deleting Rows?

Jan 25, 2014

I'm trying to remove duplicates without deleting the rows they were in (instead just being left with blank spaces).

For example Column A might look like this:

Column A

I need it to look like this:

Column A



How I can delete these duplicates without doing it one by one?

View 1 Replies View Related

Removing Duplicates From Long Row Of Data

Mar 12, 2014

I have several fields in a row that contain names of files e.g. 123.xlsx. Some fields will contain file names that will be duplicates of each other and some will be blank entries (although the blank entries can be changed to a value such as 'n/a' or 'no' etc if required).

I require only the non duplicate values to appear in the final cell, each separated with ';'.

My data is in row 2 of a spreadsheet and in every other column (A,C,E,G,I,K,M...for 45 instances in total).

I have used the following formula to identify the unique values (example below for the first four cells):

This works well and if there are several blank entries then I use a SUBSTITUTE function to change the multiple ',,,,' to a single ';'. So I only see the unique file names in the final cell, separated with ';'.

However, the above formula becomes longer and longer when each cell is added to it. I have over 40 cells that need to be added and I wondered if there was a better way of doing this?

View 6 Replies View Related

Removing Duplicates In Cells That Don't Comply With Formula?

Sep 16, 2013

I am creating a formula. The formula needs to be something like this:


Basically, I need the formula to read the value in cell A and if it is a "1" then the value in Cell G will remain, all other values in cell A will erase the values in cell G.

View 7 Replies View Related

Combine Data From One Sheet To Another - Removing Duplicates

Feb 27, 2014

I run a report each week and would like to combine it with my Updated Members workbook and then remove duplicates.

Is there a way to add this new data (ActiveList.xlsx ) to the last row of data of my (UpdatedMembers.xlsx) Sheet and remove duplicates. (I would like the new additions to stay on the bottom of my original data in the UpdatedMembers sheet, so that I can add notes to their row)

I am still learning my way around excel and all of my attempt to do this have been unsuccessful. how to pull this off.

View 1 Replies View Related

Removing Duplicates In One Column And Summarizing Data

Feb 9, 2012

I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.

An example source file is found here: [URL]...

As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.

I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.


NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |

etc etc etc

View 9 Replies View Related

Removing Duplicates Based On Latest Revision?

Aug 20, 2012

I am importing some data from a source which every time I just want to have latest revision of documents and I want it to be dynamic so that every time I import data the deletion would be automatic the data I import is something like this

DOC DEScription


and as you see I have duplicate documents with different revisions and I want to have the latest last revision only.

View 8 Replies View Related

Removing Duplicates Within Cell For Entire Column

Jun 29, 2014

Is there a code that can be written to remove duplicates within a cell for an entire column?

View 1 Replies View Related

Sorting Columns, Rows And Removing Duplicates

Dec 10, 2008

I have a single spreadsheet with a few columns but hundreds of rows. Each row is a property reference (with other data such as address in the columns) with a type of charge in column J and the amount in column K. I'll try to replicate below ...

A B ....... J K
Prop1 Prop1Address Rent 100
Prop2 Prop2Address Rent 150
Prop3 Prop3Address Gas 70
Prop4 Prop4Address Water 60
Prop4 Prop4Address Rent 200
Prop4 Prop4Address Elec 80
Prop5 Prop5Address Service 90

I want the sheet to display a property on a single row so would look like as follows ...

A B ....... Rent Gas Water Elec Service Total
Prop1 Prop1Address 100 100
Prop2 Prop2Address 150 150
Prop3 Prop3Address 70 70
Prop4 Prop4Address 200 60 80 340
Prop5 Prop5Address 90 90

View 9 Replies View Related

Removing Duplicates From List Based On Latest Date?

Jan 6, 2014

I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.

View 4 Replies View Related

Excel 2007 :: Removing Duplicates Within Time Range

May 28, 2012

I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.

Excel 2007
ABCD1EruptionI Can't Stand The Rain (78)3/7/20129:12:55
AM2EruptionI Can't Stand The Rain (78)3/7/20129:10:02
AM3ChicagoJust You 'N' Me (73)3/7/20129:05:59

[Code] .........

View 2 Replies View Related

Expanding Already Provided Formula For Removing Duplicates To Cover More Columns

Jul 20, 2013

I have a list A2...A11

In cell B1 is the following formula: =SUM(IF(FREQUENCY(IF($A$2:$A$11"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))

Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1

In cells B2...B11 is this formula: =IF(ROWS($B$2:B2)

View 6 Replies View Related

Excel 2013 :: How To Create A List In One Cell From Array While Removing Duplicates

Jul 23, 2014

------ A ------------------- B ---------Red -------Green -------Yellow ---------Red --------Yellow ------Yellow --------Blue

- C --------------- D ---
John ------------Blue, Yellow
Bill --------------Red, Yellow
Sue------------Green, Yellow, Blue

I am using Excel 2013 on Windows 7. In the above example columns A & B is the given list to process, and Columns C & D contain the result I am trying to achieve. The major part of this that I am having trouble on combining, separating them with commas in another cell, and ignoring a duplicate value. You can see bill has two red values, but I only need it displayed once in column D.

View 4 Replies View Related

Removing Duplicates Between Two Cells Reliant Upon Formatting Of Two Cells?

Jul 30, 2014

Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.

It feels like a kind of backward conditional formatting is what I am looking for

Here is my code as it stands

Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then


View 9 Replies View Related

Removing Data Based On Multiple Criteria

Mar 30, 2009

I need to remove any rows containing "EEE" in the size column.

I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.

View 13 Replies View Related

Sum Without Duplicates With Other Criteria?

Nov 20, 2013

I need to do a sum with multiple criteria and that can account for duplicate entries and select the correct duplicate to add. I have a sheet with 6 columns of data: TripDate, Company, DepartureTime, BookedTravelers, TripNumber, DataEntryDate. It's just a running data dump from multiple sources and times and can therefore have duplicate entries for a specific trip but where the number of people booked has changed and I need the sum to use the most recent number (dataentrydate is newest).

TripDate Company DepartureTime BookedTravelers TripNumber DataEntryDate
10/31/13 A 15:30 10 111 10/01/13
10/31/13 B 15:30 11 999 10/01/13
10/31/13 C 15:30 12 999 10/01/13
10/31/13 A 16:00 20 222 10/01/13
10/31/13 A 17:00 30 333 10/01/13
10/31/13 A 15:30 50 111 10/15/13
10/31/13 A 15:30 40 111 10/25/13

Currently I have this in a seperate worksheet to break down the hourly loads where A2 is a date reference I can change to what date I need, and each company is listed in a different column with hours running down Column B from 0400-2200:

=SUMIFS(BookedTravelers,TripDate,Template2!$A$2,Company,Template2!J$4,DepartureTime,">=" & $B5,DepartureTime,"

View 3 Replies View Related

Sum Duplicates With Unique Criteria?

Aug 9, 2014

col A col B col C

AA 1 dog
BB 2 cat
AA 1 cat
BB 3 cat

The end result is:

AA 1 dog
AA 1 cat
BB 5 cat

In this example there are two examples of AA, but they each have a different animal, so they need to remain separate. BB is listed twice, but has the same animal, so the total for them is 5. I am looking for a way to express this with with a formula. I tried using Sumifs, and it sort of works, but I end up with BB listed twice, and I don't need that to happen....

View 3 Replies View Related

Copyrights 2005-15, All rights reserved