How To Highlight Duplicates Based On Multiple Columns And Remove Them
Oct 8, 2011
I am trying to tell the spreadsheet that if column G *and* column V are both identical to another row's column G and V, then the entire row is a duplicate and to highlight and delete it. I also want to record a macro of this process to apply to all spreadsheets.
Here are the tutorials I am looking at, but whatever I am doing is not working the way it is working for them.
Conditional Formatting, find duplicates with multiple columns [URL]...
I am highlighting cell A2, and under Conditional Formatting, I select "Formula Is" =SUM(COUNTIF($G:$G,G2),COUNTIF($V:$V,G2))>1
Then I select the format as light orange text, but it is only highlighting A2....
That is as far as I have gotten, and I still do not know how to go about deleting the duplicate entries. I have also read there are issues with recording a macro when trying to find duplicates using more than one column in the formula, but I have not gotten that far so I can't test it.
As is likely evident in my formula and process, I am not very experienced with Excel.
View 2 Replies
ADVERTISEMENT
Jun 9, 2014
I'm running a football competition for my business where customers are required to guess World Cup football scores via Facebook. I'm storing all their entries in a spreadsheet.
In the spreadsheet column a is their name, b is the match (e.g. England v Italy), c is the home tame (England), d is the score (1-1), e is the away team (Italy)
Across from, that I have the actual fixtures taking place at the World Cup. So column I is the date, K is the home team (England), L is the actual score (2-0) and M is the away team (Italy)
What I need is a formula that when I enter the result on a game into the right set of columns (Columns I:M) it will highlight those Columns (Columns C:E) that have entered that correct score.
It needs to compare 3 columns and not just one because for example if it highlights the score (1:1 for example) it could be highlighting someone who picked Spain v Holland 1:1 where the correct result would be someone who picked England v Italy 1:1.
View 7 Replies
View Related
Oct 28, 2006
I have a column of information, and I want to find out of there are duplicates in it without alphabetizing it. Where there is a duplicate (say E2=E5), I want to check the adjoining column to see if the value of those fields are duplicates as well (does F2=F5). So, what formula would I put in D2 to see if the value of E2 has an equal anywhere in column E, and if any duplicates are found (say E10,E33, and E43), to check if F10,F33 and F43 equal F2 (the value adjoining the cell that was searched for).
to find duplicate entries in a database by using people's last names (in column E) and birthdays (in column F). Odds are if they have the same DOB and the same last name, we've found a duplicate.
View 9 Replies
View Related
Aug 28, 2007
I need to find a way to highlight duplicate records within a list. The records would first need to be compared by (1) the month (found in column A), (2) the account number (found in column B) and then (3) the commission type (found in column D).
I do not need the duplicates deleted just highlighted or better yet maybe have them identified as "Duplicates" in an empty column (such as column H).
View 9 Replies
View Related
Jul 25, 2007
I have searched the many forum solutions for duplicates however the ones I found relate to a single column. However what I would like to be able to do is starting from row 6 is to remove duplicates of multiples columns of a single worksheet, namely columns M, O, P, Q and R. Note some cells in each column are blank.
To clarify - it would firstly check for duplicates in column M, keeping just one of the rows of the duplicated value and removing entiely the row/rows of where the duplications appeared. Then go through each of the other four columns. of the worksheet doing the same for each of the other four columns.
Then after the duplicates are removed from all five columns provide a tally at the bottom of the worksheet of how many times a value now appears in the cells of each of repective columns.
View 9 Replies
View Related
Apr 4, 2013
This is a very SMALL sample of the data I need to sort through. We have been using filters but the data is growning and becoming far too time consuming using the filter method.
The output I am looking to achieve is something like this, (any format is fine; whether in rows or columns)
SVR1
1.99.99.254
(2) CAT I (display how many CAT I for SVR1)
CAT I: 2011-B-44
CAT I: ST-5546
(2) CAT II (display how many CAT II for SVR1)
CAT II: 2011-B-52
CAT II: 2011-A-21
SVR2
2.5.2.333
(1) CAT I
CAT I: 2012-E-55
(1) CAT II
CAT II: 2011-A-21
COLUMN A, varies from SVR1-SVR1400 (cointains duplicates)
COLUMN B, IP matches SVR name (contains duplicates)
COLUMN C, will be either CAT I, CAT II, CATIII
COLUMN D, will contain duplicates
I have some history of using formulas and nested formulas but this one is really throwing me for a loop. It feels like Inception to me and I'm not really sure where to start!
A
B
C
D
SVR1
1.99.99.254
[Code] .....
View 9 Replies
View Related
Dec 25, 2013
I'd like to delete (shift up) one of each set of duplicates but can't think of a way to do it
THOMAS
PHIL
PETER
JOHN
ALEX
ANDREW
THOMAS
PHIL
JOHN
JOHN
NEIL
PHIL
PHIL
THOMAS
RICHARD
BRUCE
JOHN
JOHN
PETER
NEIL
View 5 Replies
View Related
Aug 6, 2008
searching between 2 columns (A:B) and finding duplicate emails using some type of formatting that will highlight duplicate emails?
For example?
A B
me@yahoo.comsmelly@yahoo.comegg@msn.comspazz@guno.comdupada@hotmail.comhope@aol.comokay@gmail.comhelp@popp.comhope@aol.comfunky@chicken.com
check@team.com hope@aol.com
I am using MS Excel 2007 and not very experienced, but this is for a marketing golf tournament my company is hosting.
View 9 Replies
View Related
Feb 28, 2014
I want to check between column c and E if it has same values repeated more than once.
Same user has same ID twice remove one.
for Ex:
if
c2 has "Ram" and E2 has "sample001".
c5 has "Ram" and E5 has "Sample 001".
highlight the duplicate.
Check this between columns c and E till the last row.
View 1 Replies
View Related
May 11, 2014
I have following sheet:
Data from A1: BU1644
I need to check, if in A1:BU1 are any duplicate words.
All the formulas I found deal with finding duplicates downward (like A1:A1000).
Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
View 2 Replies
View Related
Nov 15, 2013
I have a data in sheet1 as below :
Excel 2012ABCDE1Sr. No.PriceSr. No.Price
21234535123453531234623123462341234712123471251234818123911861
234919135011971235023412350234812371251235125912352261235226101
2353271235327111235428123982812123552912355291312382301235630141
[Code] .........
And now looking for output in sheet2 as below :
Excel 2012ABCD1Sr. No.Price1Price2Total
21234535357031234623234641234712122451234818#N/A1861234919#N/A
1971235023423446881237125#N/A259123522626521012353272754111235428#N/A
[Code] ........
The sr.no. codes and price will keep changing in sheet1, it should automatically update in sheet2. Is it possible with either vba or formula?
View 4 Replies
View Related
Apr 10, 2013
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
Highlighted rows would be deleted:
Sheet1
*
A
B
C
D
E
F
G
1
Item
*
*
*
*
*
Imported
2
112
a
b
c
d
*
Apr-5-2013 08:53:19
[Code] ..........
View 2 Replies
View Related
Feb 10, 2014
I have an excel sheet with data where I have column A index number, B company name, c empty, d data , e with item.
[Code] ......
What I trying to do is I made a dropdown for company list and i select comp1 then i want to populate items list with out duplicates .
View 2 Replies
View Related
Jul 10, 2009
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
View 2 Replies
View Related
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
Jun 9, 2014
My spreadsheet has multiple "sessions" by date and each has three columns: a name, their organization, and a column where we want to display an "R" if they are a repeat participant. Each new session is entered to the right of the last. The names are in every third column. Like so:
name company R
name company
name company
name company R
Is it possible to search through the whole document to find repeating names, and then display an "R" in every third column if they are a repeat participant?
View 4 Replies
View Related
Nov 14, 2013
Columns A, B, and C have data (first name, last name, state) - 125k rows. Columns F, G, and H have data (first name, last name, state) - 5k rows. Some of these individuals are bound to appear on both lists, and I need to know which ones are indeed on both lists. So in other words, let's say I've got "John" "Doe" "TX" in cells F1, G1, and H1, respectively. I want to search through columns A, B, and C to find out if there are any instances in those columns of "John" "Doe" "TX" in the same row.
I'm thinking there will be multiple VLOOKUP formulas involved, but I could be way off.
View 2 Replies
View Related
Apr 14, 2014
I have database where I am searching for duplicates. The values I want to compare are not in the same column. For example:
A
B
C
D
E
[Code].....
I need to remove one of the 10 year old Yankee teams but not the 11 year old Yankee team.
View 2 Replies
View Related
May 14, 2006
I've been looking through various forums looking for a macro for a particular task, but I have not found something that works yet. Maybe someone could help me out? It's pretty straightforward situation.
I need a macro that removes an entire row when: information in column B and column C are both found in other rows ( duplicates).
I'm dealing with a worksheet with store record information ... Store Name (column B), Street Name (column C), State/Province, etc. I import new store records all the time and many are invalid because they share the same name and street address and therefore need to be removed. The ones that I’ve played with unfortunately end up deleting the cells only and not the entire row.
It's a tedious process to do manually remove entire rows (for duplicate column B&C) when dealing with thousands of rows and I would very much appreciate any assistance or insight into how simplify the process with a macro. I've attached a sample of what I'm working with.
View 9 Replies
View Related
Dec 6, 2006
I want to delete the current row if the data on row A and Row B match. Its hard to explain so I posted an example.
View 5 Replies
View Related
Nov 19, 2007
I have a worksheet that contains thousands of patient appointment entries, there are more than one entry with the same patient details contained in the worksheet. For example; sometimes a patient will reschedule a appointment, this causes another entry to added for the new appointment. When the patient attends this appointment, the appointment status changes to Seen. But the original entry still stays Rescheduled. This there a way that we can look for identical infomation in some selected columns that match the original entry but have different infomation in some other columns:
E.G. The cells in columns A,B,C,H have identical infomation than the original entry but the cell text in column D equals to "Checked" or "Cancelled" and the cell date in column G is higher than the original entry date. If the result is true then tag the original entry as Invalid
View 5 Replies
View Related
Mar 13, 2008
I am working on a list of agents and I want to get rid of the duplicates. I know I could use conditional formating to highlight the duplicate names but it conditional formating won't allow me to find duplicates based on three cells in a row. My code below works to an extent but it misses some of the duplicates in the list and I don't know why. I have check the cells of the duplicates and they are an exact match.
Sub SearchDups1()
Dim rFnd1 As Excel.Range
Dim rFnd2 As Excel.Range
Dim iCount As Integer
Dim iEnd As Integer
Dim sAdr1 As String
iCount = 2
iEnd = WorksheetFunction.Match("zzzzzzzzzz", Sheet2.Range("D:D")) + 1
Do
Set rFnd1 = Sheet2.Cells(iCount, "D")
Set rFnd2 = Sheet2.Columns("D:D").Find( _
What:=rFnd1, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)..............
View 4 Replies
View Related
Nov 8, 2012
I just want to colour duplicate values but want to do it with this Dictionary method
Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary
[Code]....
View 9 Replies
View Related
Dec 19, 2012
I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.
For example the following could work:
[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]
However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?
I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.
There is no specific pattern as to which columns would trigger the requirement.
Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
View 9 Replies
View Related
Jul 9, 2009
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................
View 12 Replies
View Related
Apr 9, 2014
I have an excel worksheet which is having duplicate values in multiple columns, i want to remove those duplicates and should return unique values... how can i do that... My Excel Sheet looks below....
View 3 Replies
View Related
May 21, 2014
I'm using the code below to remove duplicate values from a column of cells, in this case column B.
[Code] ..........
The code works fine, but I'd now like to adapt this so I can remove the duplicates from columns B, C, D and F.
I've tried over the last few days different methods but recieve 'Debug' errors.
View 8 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Nov 8, 2008
Flagging Duplicates based on Multiple conditions..
I have attached the file for reference with Dummy Data as the actual data runs in thousands...
What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records...
The Duplications has to be considered on Multiple criterions:
1. If the FNAme and LName is appearing for one Company more than once.
2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same.
3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate".
Ex: -
4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique.
Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique
5. If still the records are identical then the first occurence can be unique...
I have made this data to give an example as the actual data is very confidential.
Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic.
Please refere the Word doc Duplication Criterion Explanation for the explanation.
View 14 Replies
View Related
Jul 4, 2014
I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....
View 2 Replies
View Related
