Find Duplicate Records Based On Multiple Columns But Keep Records
Aug 10, 2014
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
View 7 Replies
ADVERTISEMENT
Apr 4, 2009
Im trying to find out if there are any duplicate records in my spreadsheet. See look at the attached. Cell 6745 down is highlighted in green. I want to check this against all the cells above. need to write a formula so that it marks any products in the green section with duplicate (in column I) if they are in fact repeated.
View 2 Replies
View Related
Mar 20, 2013
I have sales rep data with column headings as Sales Rep, Sales Date, Sales Item Code and Customer ID. There are only 2 sales reps in the data, I'll call them Rep A and Rep B. I need to extract rows where Rep A and Rep B have made sales on the same day to the same customer (it doesn't matter what type of sales). I don't want to extract sales by only one rep, it must be both reps on the same day to the same customer. I have tried a pivot table but it identifies all sales. Can this be done?
View 2 Replies
View Related
Sep 15, 2006
I have an excel spreadsheet that should have one record for each artifact in a museum collection. The problem is that the museum has consolidated this information from several different sources into one spreadsheet and now there are many duplicate records. They want all the duplicate records removed so that there is just one record for each artifact, BUT there may be different pieces of information in each of the duplicate records. So I want to do the following:
- sort records based on Accession Number (column A)
- find duplicate Accession Number records
- determine which fields (columns) within a duplicate record are unique and concatenate those entries into one master record for each Accession Number
- delete the duplicate Accession Number records
In the attached sample sheet, for Accession Number 66-1-100, we have 6 duplicate records. In the columns, we have information which in some of the records is duplicated, in some it is unique and in some it is missing completely. The museum wants just one master record for each Accession Number and they want all the data from the duplicate records concatenated into one and all the duplicates and blanks discarded.
What I've done so far:............
View 6 Replies
View Related
Feb 9, 2007
I have a very large table that has a one to multiple relationship in a GIS. GIS doesnt support that type of relation ship. I need to find records that are related to the single key but have different values than the first record for that key.
eg
Col 1 col 2 Col 3(key)
ab ab 1
bc bc 1
In this example, only the first row (ab) will be regarded in the GIS. so I need to pull out the second row and put it into another dbf based on the three columns so that I can then put it into the GIS so that the second row is considered and not ignored.
View 4 Replies
View Related
Jul 8, 2006
If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Jan 3, 2013
I have a questions regarding extracting records using a partial text search in multiple columns.
I would like to do a partial text search of the letter "j" in the Name column, and search for the number "1" in the Phone column and extract the corresponding records.
The formula I have been using is as follows: =IF(ROWS(C$2:C2)>$B$2,"",INDEX(C$2:C$6,SMALL(IF(AND(ISNUMBER(SEARCH("J",$K$25:$K$29)),
ISNUMBER(SEARCH("1",$L$25:$L$29))),ROW($J$25:$J$29)-ROW($J$25)+1),ROWS(C$2:C2))))
However, the formula is not working correctly.
find my sample data below:
A
B
C
1
Partial Text Search
[Code]....
View 5 Replies
View Related
Apr 25, 2014
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
View 5 Replies
View Related
May 2, 2008
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335
AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335
View 3 Replies
View Related
Oct 7, 2008
I have 2 columns A and B.
There are 1000 records in column A and 500 records in column B.
I would like to compare both columns for duplicate entries and deleting all instances of those record in both columns, leaving behind records that were not duplicated to begin with in their respective columns.
View 5 Replies
View Related
Jul 15, 2009
I have a spreadsheet with a column of records (text ) from B1:B4000. I would like to delete the entire row of the duplicate record. Overall data set range is (A1:I4000). This spreadsheet may contain more than 1 duplicate record. Is there in easy fix?
View 12 Replies
View Related
Mar 30, 2009
I have a single-column list. The column header is 'ID'... records within this column repeat sometimes (this is ok). I need a worksheet function that will allow me to mark the second, third, or fourth duplicate, but leave the first duplicate alone or unmarked. I have searched the Web and this forum, and came up empty. If this cannot be done with a worksheet function, then VBA is welcome. No ColorIndex.
View 2 Replies
View Related
Nov 22, 2009
I'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.
Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No
E4 No
Expected results:
E1 Yes
E2 Yes
E3 Yes
E4 Yes
View 13 Replies
View Related
May 8, 2008
In Column A, is gender: male/female
In Column B, is name: Bob, Jon, Mary, etc
In Column C, is salary: $40, $30, $20, etc.
In column B, there will be instances where a name might appear more than once. I want to be able to do a sumif formula of Column A and Column C, but not include any records where a name in column B is listed more than once. Is there a way to do this?
1 Male Bob $50
2 Female Jane $30
3 Female Kate $20
4 Male Mike $30
5 Male Bob $50
So if I wanted to sum all male salaries above, it would be $80, not $130.
View 9 Replies
View Related
Nov 14, 2006
I have a spreadsheet with 2 columns of about 2900 records. about half of the records are duplicates. How can I eliminate the duplicate records?
Example
ID / ID#
2 3456789
2 3456789
3 5678987
3 5678987
4 1122334
4 1122334
View 3 Replies
View Related
Dec 12, 2012
My spreadsheet has more than 200 columns and 5000 rows of data and blanks. When I select all columns and rows and remove the duplicates, 6 records are removed. I need to only keep and be able to identify those 6 records. I could use something like =IF(COUNTIF($A$2:A2,A2)=1,1,0) if I had only few columns that would identify dups but since there are so many columns it would be prodigious job to create one formula for each column and filter through 200.
View 1 Replies
View Related
Jun 30, 2014
I am using the following code to remove duplicate records from a sheet:
[Code] ....
It successfully removes the duplicates (based on Columns A and B), however it is changing the formatting of the cells and messing with the conditional formatting I have on the sheet.
View 14 Replies
View Related
Dec 20, 2008
I have a spreadsheet that will export records when I "click" a button, to a spreadsheet on a network drive.
I would like to attach some code that will check to see if a duplicate record is going to be written to the network drive, and if so, alert the user that they are about to create a duplicate . If it is a duplicate, give the user the option to overwrite the existing record.
Ex. I export the results for account 12345.....Account 12345 is now written to an outside sheet.
User2 logs in and works with customer 12345, w/out knowing that someone has already worked with this account, presses the export button.....This is the trigger point for what I want to happen. At this point when it locates a dupe in the outside spreadsheet it will prompt the user too make some chouces .
find attached the code I would like to attach this to...
HTML option Explicit
Sub TransferData(Optional Dummy As Long)
Dim Row As Long
Dim TargetRow As Long
Dim Path As String
Dim Prompt As String
Dim Title As String
Dim Cel As Range
Dim Wkb As Workbook
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Set Cel = wsCustomers.Range("C:C").Find(What:=Range("C4").Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
Prompt = "There is no match for the Customer Number (" & Range("C4").Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
Row = Cel.Row
Set Cel = wsSetup.Range("A:A").Find(What:=wsCustomers.Range("A" & Row).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
Prompt = "There is no Workbook Path setup for this Region (" & wsCustomers.Range("A" & Row).Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
Path = wsSetup.Range("B" & Cel.Row).Value
If Trim(Path) = "" Then
Prompt = "There is no Workbook Path setup for this Customer Number (" & Range("C4").Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
On Error Resume Next
Set Wkb = Workbooks.Open(Filename:=Path, UpdateLinks:=False)
On Error GoTo 0
If Wkb Is Nothing Then
Prompt = "The workbook for this Customer Number (" & Range("C4").Value & ") could not be opened." & vbNewLine & vbNewLine & "Path: " & Path
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
View 10 Replies
View Related
Jan 31, 2012
i have data as ( name , Father name ,CNIC, and there salaries , but there is repeating in this data so i want to add their salaries to one name and delete duplication , it should be done in excel. this data is so long .
View 2 Replies
View Related
Nov 21, 2012
I have following data on my excel sheet.
man
man
man
man ....
john
john
john
john ......
10 man cells are there, and 20 john cells are there.
I need 10% of this data.
It means 1 man, 2 john.
View 3 Replies
View Related
Apr 12, 2013
I have spent the last couple hours compiling a large amount of data. I have a list of websites, each have a number of visits and transactions. The list is many thousands strong so i have a huge amount of data to work with but there are a lot of duplicate sites. See below, I have taken a snapshot of some of it:
I've highlighted a duplicate example.
Would it be possible across the entire data set to sum all of the duplicate rows into one. So, for the 3 shown, to be reduced to one row with 228 visits and 2 transactions?
And to do it quickly? As I could go through them manually but I don't have to time to go through 10,000 rows.
View 4 Replies
View Related
Aug 20, 2008
Wondering if there is a way, script-wise or formula-wise that will look through a table and find duplicates but not just any duplicates, it has to match a certain criteria.
For example, I have a column for UPC and Vendor#. I have my table sorted by vendor (all 159 of them in one table) I want to find out whether a UPC appears more than once for a vendor.
UPC --- Vendor
11254 135
11251 135
11251 135
11254 9345
11251 9345
Here, as you can see, UPC 11251 appeared more than once for vendor 135. Can this be done through conditional formatting ? or a script?
View 9 Replies
View Related
Sep 21, 2006
In my sheet I have it search for duplicate records and give those records the same id. Is there anyway to combine data into one record. For example, I have a person with a value in column Points1, on another row a value in column Points2, and another row a value in column Points3. I want take those values from the duplicate records and put them all in one record. I've attached a sample.
View 2 Replies
View Related
Jan 28, 2014
Any way of Deleting duplicate records, using Excel 2003.
The raw data is in the form of a text string. For example:
Vehicle 123456_F_AB 280
Vehicle 123456_R_AB 147
So when I do a count of these cells, I obtain a count of 2. '=Count(A1)'. How can I easily exclude 1 of the duplicates (123456). It needs to be a user friendly solution as other people may have to run this when I'm on leave?
View 2 Replies
View Related
Oct 16, 2008
Through a query I extract data from an oracle database. This database creates a record for every unique Article_Batch_Pallet_Faultnumber combination. Through a filter macro I create a new format on a different worksheet which creates a record for every uniqe Article_Batch_Pallet combination. I need a searchfunction to get the faultnumbers in the same row.
View 2 Replies
View Related
Mar 27, 2008
I have a list of customers that contains duplicate records. These records ALSO include thier addresses WHICH may or may not be identical.
Example: Customer with LAST NAME in A1, FIRST NAME in B1 may be the same person as A2 and B2, however each record may have a different address in column C1 and C2 respectively.
I want to be able to filter out all duplicate customers based soley on the first and last name (data in columns A and B).
View 9 Replies
View Related
Feb 13, 2009
I have to load the data in Sheet 3 but before I do that I have to make sure that any organisation name that already exist in either Sheet 1 or Sheet 2 should be removed from Sheet 3.
Sheet 1 has 226 Organisation Names
Sheet 2 has 62 Organisation Names
Sheet 3 has 664 Organisation Names
I do not know how to write a Macro, nor d o I know how to write code.
Can someone help with a formular or code? or is there another simple way to do it?
View 9 Replies
View Related
Feb 9, 2013
I have a created recording sheet (attached) for the purpose of recording moths. I send this sheet out to those recorders who live in my county and at the end of the year they send the filled in sheet back to me. Over time I have put safeguards onto the sheet to ensure that I get the cleanest data returned to me. However, there is still one area which is giving me problems - users entering duplicate records.
Some formula which will stop recorders from entering those duplicate records. There are many columns on the sheet which recorders have to fill in with information, but as far as the duplicate records are concerned it is where the same information is entered into columns C, F and J that I'm having a problem with. It would be great if, when the same information was entered in these columns that the affected cells were highlighted in some way and if an error message appeared saying 'Duplicate record, either delete or alter grid reference'
I have entered two test duplicate record on the 'macro moths' sheet of the attached file : Test Recording sheet.xls
View 14 Replies
View Related
Feb 17, 2014
i have to removed the duplciate records with older dates. The date column is made up of date and time.
IDExpiry Date
1202 Mar 2013 12:57:04
1220 Apr 2013 10:49:24
5020 Apr 2013 10:57:06
5021 Oct 2013 07:20:54
5024 Oct 2013 08:41:56
View 3 Replies
View Related
