Extracting Duplicate Records With Multiple Criteria

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


ADVERTISEMENT

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 View Related

Extracting Records With Partial Text In Multiple Columns?

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

Concatenating Data From Multiple Duplicate Records

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

One Criteria - Return Multiple Records

Jan 16, 2009

I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example
Col A Col B Col C
Row 1 Apple Fruit 12
Row 2 Banana Fruit 15
Row 3 Carrot Veg 13

I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.

View 9 Replies View Related

Count Unique Records With Multiple Criteria With Formula

Sep 18, 2006

Count unique records in Column B where.

1.)
... Column H >=A1 and <=A2

2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

View 7 Replies View Related

Extracting Certain Rows Based On Multiple Criteria?

Oct 29, 2013

There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.

Example.png

Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).

View 6 Replies View Related

Extracting Data From A Table - Multiple Criteria

Jul 31, 2008

I have a data table that has the following structure


Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33



I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.

I can use the following index / match formula to extract the salary that matches the job title and location.

(INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1

However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)

View 9 Replies View Related

Extracting Unique Data Based On Multiple Criteria With Formulas

Sep 27, 2013

I have data and after doing some data manipulation it looks like this

Job #
Part #
Lot #
Total
Grand Total

[Code]..

What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.

View 2 Replies View Related

Extracting Data Based On Multiple Criteria - Index Match Array?

Jan 2, 2013

I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.

Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.

Results can either be "ok", "warning", "Alert" only

View 6 Replies View Related

Extracting Records Without Duplication

May 5, 2008

i am having data in ColA & ColB in both Sheet1 & Sheet2

1) some records are both in Sheet1 & Sheet2
2) in Sheet1 itself some records are duplicated
3) in Sheet2 itself some records are duplicated

what i want is to extract the all the records in Sheet1 & Sheet2 in Sheet3
without duplication of records..!

View 10 Replies View Related

Search For Duplicate Using Multiple Criteria

Jun 21, 2013

I need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.

example

Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs

View 2 Replies View Related

ADVANCED: Extracting Unique Records

Oct 25, 2006

At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.

So here it is:

I have two lists of sales.

1. our list from our point of sale system with order numbers and other details, entered by the sales agent.

2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc

The two lists need to be compared to see what sales are missing from either one..

so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.

IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...

Here is an example of the data i am using:

ORDER #
1630923-
1634849..
1634849..
1634972+

the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.

so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..

and the order numbers with "-" next to them are from our list

and the order numbers with "+" next to them are from THEIR list

i want to end up with a list like this:

ORDER #
1630923
1634972
1635643
1641970
1648112
1649716
1653854

View 10 Replies View Related

Conditional Formatting Duplicate Value With Multiple Criteria?

Aug 21, 2013

formatiing a cell, e.g. change color to RED, in the event the value is a duplicate. To make it clear, I need to show that if the Serial Number of a product just entered is a duplicate and should change color when a combination of conditions is true. i.e. if a "part number" AND the currently entered "serial number" already exist, the entered serial numbers should change color.

View 3 Replies View Related

Extracting Records Based On Expiry Dates

Jan 12, 2010

I have created a database at work which collates various information on the sale of products. Part of this information is the date that each particular product is up for review (In DD,MM,YYY) format. Using the internal clock function I have produced a formula that calculates the numeber of days until expiry.

However, using this information I want to be able to run a macro that looks at accounts that have for instance 5 days left until expiary and populate a new worksheet with various information extracted from my 'main' database. For example, search all the porducts that are due to expire tomorrow and then populate my new workseet with data from cells such as

Product 1 = Cell A1, Cell a2, Cell a6, Cell a7,
Prodcut 2 = a1, a2, a6, a7,
Product 39 = a1, a2, a6, a7

View 2 Replies View Related

Extracting Date Related Records In Pivot Table

Mar 19, 2007

I have a master list that is updated in real time by linked worksheets from several people. While my actual report covers 7 managers and 7 different products (“contract types” in this example), I have summarized and simplified the input in the attached file. There are three pertinent dates – the date assigned to the manager (col A), the date completed by the manager (col F), and the date approved by the executive (Col G).

These dates will span month ends and I need to be able to provide monthly reports that provide details on the number of contracts and the dollar value all contracts assigned and completed by the manager during the month (say, February) as well as any contracts assigned in any month which remain uncompleted at the current month-end (e.g. the $7,500 Smith contract assigned on Feb 21st that remains uncompleted and the Feb 26th $200 Jones contract which was uncompleted at Feb 28th, but completed in early March, in this example). Can this be done directly in a Pivot table from the full master list, or will I need to have separate sheets in my workbook where I extract the pertinent records for that month?

View 2 Replies View Related

Delete Duplicate Rows Based On Multiple Cell Criteria

Apr 21, 2007

I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).

How do I modify the code below to evaluate duplicate data in Columns A through D? .....

View 7 Replies View Related

Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2

View 3 Replies View Related

Deleting Duplicate Records (both)

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

Delete Duplicate Records

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

Subsequent Duplicate Records

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

Delete Of Duplicate Records

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

Sumif Without Duplicate Records

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

Eliminate Duplicate Records

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

Keep Only Duplicate Records And Remove The Rest

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

Remove Duplicate Records From Sheet?

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

Duplicate Check On Export Of Records

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

Adding Values Against Duplicate Records

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

Duplicate Records Extraction With Percentage

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

Consolidate Duplicate Fields / Records?

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







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