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):
=A2&IF(C2=A2,"",","&C2)&IF(OR(E2=A2,E2=C2),"",","&E2)&IF(OR(G2=A2,G2=C2,G2=E2),"",","&G2)

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


ADVERTISEMENT

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:
Sue1/3/2014Mike8/8/2014
Jay25/4/2013Sue1/3/2014
Jay25/4/2013Jay25/4/2013
Mike8/8/2014
Mike8/8/2014
Sue1/3/2014
Sue25/6/2012
Sue1/3/2014
Sue5/7/2012
Jay2/2/2011
Mike5/5/2010

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

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.

IDEAL COLUMN HEADINGS:

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

etc etc etc

View 9 Replies View Related

Removing All But Highest Occurrence Of Version Numbers In Long List?

Feb 7, 2014

I have a list full of different IDs with different version numbers (This format: AKH123.1).

I'm trying to clean the list - removing all duplicate IDs but leaving only the largest version number.

I managed to remove the last digit using LEN/VLOOKUP and removing duplicates, but leaving only the largest version number is too tricky for me. I've thought about trying something with LARGE but can't wrap my mind around it.

My thought process went like this: I want to find all occurrences of "AKH123" and return the largest one.

The list contains about 8000 entries and this would save en enormous amount of work.

Here is a tiny portion of the list:

AKH450.1
AKH451.1
AKH451.3
AKH451.4
AKH451.5
AKH451.6
AKH451.7
AKH451.8
AKH452.1
AKH453.3
AKH454.1
AKH455.1
AKH455.2

View 3 Replies View Related

Different Long Numbers Formatted As Text Are Considered Duplicates

Jan 9, 2014

I have an issue with long numbers (16 digits). To display them correctly in excel I formatted the cells as text. This part works fine, but now for some reason conditional formatting marks different numbers as duplicates.

In my case 3081281170122602 and 3081281170122601 are considered as duplicates by excel.

See the file attached. duplikaat.xlsx

View 4 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)

against

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

Example:
Name Col-1 Col-2 Col-3 Col-4
abc 1 3 4
abc 1 2 3 4
abc
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 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 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

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
Sheets("Hypo_tax").Select

[Code].....

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
1
1
1
1
2
2
2
3

I need it to look like this:

Column A
1

2

3

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

View 1 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:

[Code]...

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

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 NO.
DOC DEScription
status

[Code]....

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

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

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
John123@gmail.com--------Blue
Bill323@gmail.com ---------Red
Sue223@gmail.com -------Green
Sue223@gmail.com -------Yellow
Bill323@gmail.com ---------Red
Bill323@gmail.com --------Yellow
John123@gmail.com ------Yellow
Sue223@gmail.com --------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

[Code]....

View 9 Replies View Related

Convert Data From Table Format Into One Long Row Of Data?

Jun 17, 2013

I have a sheet with data 7 columns wide and up to 1500 rows long. Below are a few lines from the sheet. The data from the sheet is grouped into "months" as shown in the header down the first column, "Show June, 2013..." and "Show July, 2013...", and so on. Is there a way I can make Excel re-align the data into ONE long row of data for each month? Each new month row must be "triggered" by the text (like the word "Show") because the actual cell number of where the new month begins may change with new updates. I want to do this because I plan to refresh the data once at the end of each day from the web and apply the new data to the "next" row of data. So eventually, I can have a history of "Last" values I can chart for each "strike" for each month.

CALLS
PUTS

Show June, 2013 Options Hide June, 2013 Options

Symbol
StrikePrice
Vol
Last
Last
Vol
Symbol

quote
15.00
2.00
14.25
0.02
22.00
quote

[Code] ........

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved