Deleting Non-duplicates
Sep 7, 2008
I have a large spreadsheet of over 5,000 rows and 20 columns. What I would like to be able to do is find a formula(?) that will enable me to retain rows of data when Cells in column B and column E contain duplicate data but delete entire rows if the contents of cells in column B and E are not duplicated.
For example:
Column B contains Part Numbers
Column E contains Manufacturer
If a part number (Cell B)has been made by two or more manufacturers (Cell E), retain all relevant rows.
If a part number has only been made by one manufacturer, however many times, delete all relevant rows.
What I'm trying to be able to achieve is retain data so that I can analyse it when 2 or more manufacturers have produced the same parts.
View 9 Replies
ADVERTISEMENT
May 7, 2009
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies
View Related
Jan 27, 2014
sample3.xlsx
I'm still having issues with this workbook. I cannot use a pivot table to fix it, I don't understand them and it confuses me greatly..... so I'm at the mercy of either a formula or macro. I need to combine the duplicate part numbers (a), total the quantities, average the price (d), and total the amount of the part (e). I'm having a very difficult time with it.
(I have a sample attached that is file sample 2, and the entire spreadsheet attached- sample 3)
View 4 Replies
View Related
Mar 22, 2013
I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A). I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B. So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.
A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657
So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.
View 7 Replies
View Related
May 13, 2014
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
View 4 Replies
View Related
Feb 24, 2010
Does anyone have a utility which can be used to select a specific column i.e. B:B, which then 'selects' all duplicate values in that column prior to deleting them ?
View 4 Replies
View Related
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
Aug 28, 2009
way to join two or more tables in excel adding new rows and columns.
In this way:
Input TABLE1
ID, Tag1, Tag2
1, alfa, big
2, delta, small
Input TABLE2
ID, Tag1, Tag3
2, delta, green
3, gamma, yellow
Output RESULTING TABLE
ID, Tag1, Tag2, Tag3
1, alfa, big, -
2, delta, small, green
3, gamma, -, yellow
View 9 Replies
View Related
Apr 18, 2006
I have some Job numbers that are duplicates. I can't just do a loop and delete any duplicates that come across because I have to be sure the right one is deleted.
I want the old data to be deleted if there is a duplicate..I just don't know how to verify which one is the old one using VBA. I was thinking of maybe using an advanced filter, but I don't know enough about them.
Is there code that will check both duplicates and delete the old one?
View 5 Replies
View Related
Mar 13, 2009
I have a report created daily with account numbers, however there are duplicates. Is there a faster way to filter out duplicates other than sorting and deleting manually?
View 4 Replies
View Related
Feb 26, 2012
I am conducting a stock count.
Column A consists of a list of the barcodes I've scanned.
In column A there is sometimes more than one of the same barcode when i have more than one of the same product. is there a way of deleting duplicate barcodes in column a and replace them with a 'Quantity' column?
View 2 Replies
View Related
May 16, 2006
I have 2 huge lists. One is " the Master list" which I have to extract items that are not already listed on the second list . I don't want to remove duplicate entries,I want to remove the duplicates completely. in both lists, so that I only have items that are not in the second list...
like this..
List 1:
# $ % & * ! @
List 2:
# & @
so I want to end up with:
$ % * !
View 8 Replies
View Related
Mar 17, 2007
I have two seperate lists. One on Sheet 1 and One on sheet 2. What I need to do is take the list on sheet 2 and compare it to the list on sheet 1. If any of the items appear on sheet one, I need to delete those items form sheet 2.
So in the end the only items left on sheet 2 will be items that dont match any items on sheet 1
View 9 Replies
View Related
Apr 6, 2014
I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....
View 13 Replies
View Related
Aug 24, 2011
In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
View 3 Replies
View Related
Mar 23, 2014
I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.
Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.
On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.
And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.
Is there a formula to count these as one weld:
1692-SG-0040-04_05
1692-SG-0040-04_05R1
1692-SG-0040-04_05R2
This as one weld:
6317-FG-1690-02_06
6317-FG-1690-02_06C1
6317-FG-1690-02_06C2
...and this as one weld:
1698-SG-0077-01_04
1698-SG-0077-01_04C1
1698-SG-0077-01_04C1R1
View 7 Replies
View Related
Jan 24, 2012
I am having trouble creating a function to count duplicates of duplicates.
An example of the data table 1 is:
Product 1 2nd
Product 1 2nd
Product 1 New
Product 1 New
Product 1 Flt
Product 2 2nd
Product 2 New
Product 2 New
Product 2 Flt
Product 2 Flt
Product 3 2nd
Product 3 2nd
Product 3 2nd
Product 3 New
Product 3 Flt
I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:
New 2nd Flt
Product 1 XX XX XX
Product 2 XX XX XX
Product 3 XX XX XX
I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.
I am using Windows 7 and MS Office 2010.
View 2 Replies
View Related
Apr 8, 2014
I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
View 7 Replies
View Related
Jun 23, 2006
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View 3 Replies
View Related
Apr 6, 2008
i have created a user from called "delete"
from this the plan is that you will be able to select the name of the tab that you wish to delete from a drop down list (which will contain all the tab names) and click on a command button and this will then delete it
View 9 Replies
View Related
Dec 28, 2009
I am in need of code that will match a variable, "TheName", to the values in a range named "Phases". If a match is made then I want to delete the entire row which the value is matched in.
example:
PHASES
cat
dog
pig
My variable, "TheName" = Cat
I want the code search only the range "Phases" and delete the entire row in which the variable "TheName" is found. so in this case it would delete the entire row in which the value of "cat" was matched.
View 9 Replies
View Related
Mar 31, 2007
In Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.
View 12 Replies
View Related
Jan 8, 2009
I have data in a column that has numbers letters and characters in, all that I require is the first 9 numbers (these are always the first 9 characters), any idea how this can be achieved.
View 5 Replies
View Related
Nov 10, 2009
I have a long list of names with their locations in brackets. What formula should i use to delete the brackets and their contents only?
View 3 Replies
View Related
Feb 22, 2007
I would like to run a macro that will delete the selected row in a protected worksheet but only delete it if the row does not have locked cells. I want users to be able to delete the rows they no longer need but only be able to delete them if the row does not have formulas that are locked.
BTW-I am working in an older version of Excel.
View 12 Replies
View Related
Aug 15, 2007
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies
View Related
Jul 9, 2008
Checks if there is a sheet name and if there is a sheet of that name then it is deleted.
the sheet name is a number entered through an inputbox and "Average"
View 10 Replies
View Related
Nov 7, 2008
Just finished amending a piece of code to enable me to search for an agents name in a list of files. This is then copied to a new worksheet (named by the name entered in the search). What I need is to cut(or delete) the row from the original sheet (sheet 1 in this case) thus leaving me the remaining list of files.
Below is how the code is setup at the moment....(just copies the row)
View 12 Replies
View Related
Dec 26, 2008
Deleting Columns. I've got 2 codes here,
This:
View 3 Replies
View Related
Jan 2, 2009
I have a column that has all the digits in the phone number. What I would like to do is remove the first 8 characters example 111-222-3333. I want to remove all the ones dash twos and dash. Can someone help me please ?
View 7 Replies
View Related
