Highlight Duplicates Between Multiple Columns?
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
ADVERTISEMENT
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
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
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
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
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
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
Nov 9, 2009
how to highlight duplicates in Excel?
I have 3 columns A: surnames, B: names and C: dates of birth. I have already used filtering to sort the data, however I have too many names ( many thouands) to look through them to ldelete duplicates.
I was thinking about something like =IF(A1:A3=B1:B3; "1"), but it doesn't work.
View 9 Replies
View Related
Jan 12, 2007
I have a sheet that I use to go through and check data that is returned to me from clientel. Currently I am working on getting it to check for duplicates and having a heck of a time. Here is how I would like it to work. check the comment on all the cells in row 1. If the comment box has the keyword unique anywhere in its text then select that column, and highlight all duplicate entries. I have found plenty of examples on how to get it to highlight duplicates, but nothing close about trying to do it within the same contexts as I am trying.
View 2 Replies
View Related
May 10, 2014
I have attached the excel sheet for reference. As shown in the "After" sheet, i need the 2nd to nth instance of duplicate keywords to be highlighted with some color.
View 9 Replies
View Related
Oct 4, 2007
I'm using this conditional formatting to spot duplicate data by turning it red:
= COUNTIF(C:C,C6650)>1
How can I also get it to put a '1' in column A if it is not a duplicate?
View 9 Replies
View Related
Nov 7, 2013
Is there a way of getting excel to highlight duplicates within 2 separate sheets, one beside the other on screen?
View 14 Replies
View Related
Jun 12, 2014
I am using below code to highlight duplicates with different colors.
I want to change the range in B3:C2000
[Code] .....
View 3 Replies
View Related
Aug 11, 2014
I am trying to highlight duplicates across a workbook of 12 sheets. Essentially we are using it to track ID#s and I want to highlight the cell if this the value in cells A4:A100 matches that of the same sell range in the other sheets. I was able to come up with the below code, but even when I use in the "This Workbook" it only applies it within a sheet, and not across the multiple sheets.
VB: ...........
Formula: ..........
View 5 Replies
View Related
Oct 4, 2008
I have a worksheet that has times based on 7 minute intervals. If I post 10:37 AM in cell b5 then again in cell b6 I would like b6 to highlight.
I'm trying to use countif($b$3:$b$15,b2)>1 to highlight duplicate times in column B. For some reason it will not work for me. Should I use something different?
View 2 Replies
View Related
Jul 3, 2007
I receive a daily spreadsheet which contains (amongst other information) a column of postcodes. I'd like to run a macro which compares these postcodes with a list of other postcodes, and then to colour highlight each instance of that post code in the daily spreadhseet.
I have been able to find and modify a macro but that searches for one post code at a time and then adds a comments box to the cell.
Instead of copying that piece of code and editing it for the several hundred post codes I need to "look up" against, I'd like to know how to create a list of post codes in the macro, so that the macro searches the first post code in the lookup list, finds and highlights each cell instance, then moves onto the next post code in the lookup list, until the end of the lookup list. I would like this as the editing of the lookup list would be much easier than editing paragraphs of code.
View 9 Replies
View Related
Dec 17, 2009
I am trying to teach myself something new, say I have a list of numbers in two rows and I want to count the numbers and have it tell me if there are duplicates and highlight the duplicates. I got the part about getting Excel to tell me if there are duplicates but I can not seem to figure out the conditional formating part. I uploaded a sheet.
View 3 Replies
View Related
Feb 8, 2011
I have one debit and one credit column. I want to higlight duplicates, with positive and negative values.
Like this.
Column A
5000
1000
10000
3000
Column B
- 10000
-5000
-3000
-2000
I know how to do the conditional formating to find duplicates when there is only positive or negative numbers, but i don't know how to match both positive and negative numbers.
View 9 Replies
View Related
Sep 4, 2008
I have a roster for a large group in excel and would like to have an easy way to highlight if there are duplicate entries in the roster as we are merging multiple smaller lists together.
View 9 Replies
View Related
Dec 3, 2008
A:2007 Email Addresses
john@yahoo.com
Sam@hotmail.com
etc
etc
>6000 more email address9000 more email addresses)
I need to match up & clearly highlight the pairs (duplicates) in each coloumn.
What's the trick for this?
View 9 Replies
View Related
Oct 2, 2009
I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.
I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - DUPLICATES - Many Macros.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA29=ABCD29 PN3215 301SOUTHERN APPROACH9107 313NORTHERN APPROACH9105 322GENERAL UNDERSIDE OF WESTERN STONE ARCH9111 331WESTERN ELEVATION9104 343GENERAL UNDERSIDE9110 35 36 PN7583 371SOUTHERN APPROACH9111 382NORTHERN APPROACH9112 391GENERAL UNDERSIDE9113 Sheet 9 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Feb 21, 2009
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
View 5 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
Mar 17, 2014
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
View 1 Replies
View Related
Sep 27, 2011
I have two columns of data, I and M and need to find the cases where duplicates occure in I but the data in M does not match. If the data in M matches then it's ok. I have column I sorted A to Z.
View 1 Replies
View Related
Feb 21, 2009
I have a list of numbers in column A of my Worksheet, and a list of names in column B.
There are duplicates in both columns:
A B
234 John Smith
253 Charles White
461 Mary Carlsson
876 Erica Alvin
954 Joe Brown
234 John Smith
461 Mary Carlsson
The duplicates in column A and in column B are correct, because the same names correspond to the same numbers. I would like a formula or VBA macro, if possible, to detect when there is no correspondence, for example, if the last row above were
A B
461 Ben Wayne - where it should be Mary Carlsson.
Is it possible to check all the rows of both columns and identify errors?
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
