Delete Row When Value In Column Is Unique
Sep 22, 2006
I have a worksheet called 'DR manager and deceased' which is part of a larger workbook. It contains around 20000 entries. There should be at least 2 rows for each entry, identified by the customer erefrence in column H, however a casting a quick eye down the data shows me that there are several entries where there is only one row for a specific customer reference and I need to identify these. I need a macro that will remove the entire row from this worksheet and copy it to the 'unique' worksheet if the value in column H for that row is unique, ie only found once in the whole of column H.
View 4 Replies
ADVERTISEMENT
Feb 23, 2012
I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.
View 9 Replies
View Related
Dec 28, 2006
This is an example of my table , there are more columns but here is apart of it:
id Product Name
1Frnt Fndr CR125/250 black
1Frnt Fndr CR125/250 white
1Frnt Fndr CR125/250 silver
1Frnt Fndr CR125/250 00 cr red
2Frnt Fndr CR125/250/450/500 black
2Frnt Fndr CR125/250/450/500 white
2Frnt Fndr CR125/250/450/500 00 cr red
3Frnt Fndr CR125/250/500 black
3Frnt Fndr CR125/250/500 white
3Frnt Fndr CR125/250/500 florescent red
4Frnt Fndr CR80/85 black
4Frnt Fndr CR80/85 00 cr red
I need to delete all the rows that do not have a unique ID:
Frome the table above I need it to return something like this
id Product Name
1Frnt Fndr CR125/250 black
2Frnt Fndr CR125/250/450/500 black
3Frnt Fndr CR125/250/500 black
4Frnt Fndr CR80/85 black
check out the file
View 10 Replies
View Related
Jun 12, 2008
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.
Sub Dummy()
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........
View 9 Replies
View Related
Jul 20, 2007
i'm looking to write some code that will filter through a list and either identify, or even better delete rows that aren't unique.
here's an example
xyz1
xyz1
xyz2
xyz2
xyz2
from the above lines, i'd like to see xyz1 deleted, and the first two lines of xyz2 deleted.
(obviously this is a very basic version of what i want, but it gives an idea)
also, the lines may be out of order
i.e.
xyz2
xyz1
xyz2
xyz1
xyz2
- a sort can be done so it's not that important that they're out of order...
View 7 Replies
View Related
Apr 7, 2009
I need is for the below code to not delete anything if the date is within a certain range. I want to keep all rows which have a date of 11 days past the current date. So for example. If today is 04/07/2009 then the macro should only delete unique values from 04/19/2009 and on.
Column A is the Item # and column D is expiration date.
View 3 Replies
View Related
Aug 22, 2013
I have worksheet, whose snapshot is given under (in reality, there are over 2000+ rows in worksheets)
I want a Macro which can perform following function in entire worksheet used range
Only retain the rows where the cell value in current row is identical BOTH in Column B, as well as column D in its immediate previous row
i.e. B14=B13 and ALSO D14=D13 (both condition should be valid, if not Delete entire ROW, i.e. having unique value)
Normally, ppl wish to delete the duplicates, but i want to retain the duplicate as identical data is of two different dates, control in Column O
My Worksheet looks like - [URL]
View 8 Replies
View Related
Sep 22, 2009
Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has
ACLU0403598
ACLU0403598
ACLU0403598
ACLU0406600
ACLU5165518
ACLU0406581
ACLU0406581
All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.
View 6 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Apr 24, 2014
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies
View Related
Jan 8, 2014
How would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
View 2 Replies
View Related
Aug 28, 2009
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.
View 2 Replies
View Related
Dec 19, 2010
I would need to count unique frequencies in column A, but only for a specified value in column B.
Example:
A|B
1|1
1|1
1|1
2|1
2|1
2|2
2|2
3|2
3|2
I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.
View 9 Replies
View Related
May 19, 2006
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies
View Related
Apr 21, 2014
I have a worksheet with Coulmn A and B , wherein , Column A has a list of values that can be duplicated. Based on the values in Column A, I want to fill corresponding cells in Column B. see the attached.
way to accomplish the same. Tried VLOOKUP.
View 1 Replies
View Related
Jul 25, 2014
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
View 10 Replies
View Related
Oct 18, 2011
How do I count unique values in Column A only if the values in Column B match?
A B
1 a
2 a
1 a
4 b
5 b
1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
View 5 Replies
View Related
Jun 24, 2009
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
View 4 Replies
View Related
May 29, 2014
I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns
Like "Service Order ID" is 1 column in set of data ,it move to second column of top row
Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,
Creation Date,Priority,Net Value,Currency,External Reference,Reference Date)
I want Get output result in same sheet (Actual).
View 3 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related
Dec 19, 2008
I have for input one column A with different values:
1
2
3
2
I nedd for output one column B with unique values from column A:
1
2
3
How can I do that?
View 5 Replies
View Related
Mar 28, 2008
Need to find a way to flag the unique number in column with 641892
2057988-0380121611AAAAA########70002057988-0380121611641892########59262057988-0380121611642176########53442057988-0380121611646570########63032057988-0380121611645812########56232057988-0380121611643573########55912057988-0380121611640502########62482057988-0380121611645723########58592057988-0380121611645818########61662057988-0380121611641295########55712057988-0380121611643574########61452057988-0380121611640079########63042057988-0380121611640450########61252057988-0380121611645494########57392057988-0380121611644573########51832057988-0380121611643595########51842057988-0380121611645404########56442057988-0380121611646669########57812057988-0380121611646890########63052057988-0380121611646013########63022057988-0380121611646755########51822057988-0380121611642278########52732057988-0380121611641892########79262057988-0380121611640787########51802057988-0380121611640774########5426
View 26 Replies
View Related
Mar 7, 2008
What's unique about row and column ranges compared to vanilla ranges?
1. This works (it calculates the deviation of each column in a range): ...
View 9 Replies
View Related
Mar 18, 2009
I'd like a macro that does 3 things..
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
View 11 Replies
View Related
Dec 8, 2009
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies
View Related
Jan 20, 2013
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
View 3 Replies
View Related
Nov 12, 2007
HOW TO pull the unique values out of a column of entries and put them into another column.
For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
View 14 Replies
View Related
Jan 11, 2013
I would like to place a formula in cells A2 down that does a running count of unique combinations of columns B and C.
For example, the first record is a white dog, so at that point there is 1 category of dog, next is a brown dog, so there there is now 2 categories etc.
I initially tried to adapt an array formula 1/COUNTIF($B$2:$B8,$B$2:$B8) but this wont quite do the trick.
View 9 Replies
View Related
Jul 10, 2013
I have a spreadsheet with unique identifiers.
I would like to add a column from another spreadsheet which also has unique identifiers - but some may be missing and there may be some missing so cannot simply cut and paste over
Merging the two sheets by this unique identifier?
View 1 Replies
View Related
May 30, 2012
How to separate unique values to another column in Excel apart from using Advanced filter..,, that is by using formula??
Since I need to separate unique values dynamically, i'm in need of formulas to do that...
View 5 Replies
View Related