I'm trying to condense my email lists in order to stop people receiving the same email having signed up to several lists. How do I compare 5 different columns to find email addresses which appear in more than one...
View 1 Replies
This should be an easy one, it has slipped my mind.
I have columns A and B, I want to find any names that appear in both column A and column B. I want them to show in a certain color.
I have data that has about 10 duplicate values (UTC Time) in one column and another column with number values (depth ft) that vary. I need to obtain the maximum (highest) value in the depth column and remove the other duplicates to filter out the low values. So for the data example below for UTC 15:56:28 I only want the 5.7 row, for 15:56:29 I want5.3 row and so on. I can attach the sheet. - this is a huuge dataset so manual filtering wont work. Data is from a sonar that gives 10 depth readings per second - I only need one depth tat is the highest value.
IDUTCDepth (FT)
115:56:284.3
215:56:284.4
315:56:285
415:56:285.4
515:56:285.7
615:56:285.5
715:56:285.6..........
I have a workbook that I want to find if I have any duplicate numbers in a specific area.
The area of cells that I am checking is C3 through AO70
I am checking for numbers between 95 and 800. These are all ID# of individuals and not all the numbers between 95 and 800 are used. IE: 97 through 100 are not used ect.
I have already written a macro that does something else and I can use it to check each number as it comes up. However, once the number comes up I don't know how to use it to check the area.
If I can check all the area at one time to find duplicates it would be easier.
I do not know how to do either way but I can adapt my macro to whatever way is possible.
I have a workbook of appointments, I need a formula to list duplicate appointments and display them using 2 criteria (date and time).
See attached workbook : duplicate entries.xlsx
Just wondering if anyone has a macro, or formula that would allow me to find out (and possibly highlight), when any value in column A is equal to any value in column B. Im dealing with about 2000 rows so its almost impossible to complete manually.
Sorry the heading is supposed to read need help finding duplicates between 2 COLUMNS
way to wrap or format anything in this post. I dont think the text I put here is code, but I want to be sure, after receiving a moderators infraction for failing to properly wrap code in a previous post.
Now:
I have a wks in which the first column is a list account #'s and the folowing columns are specifics of transactions or interactions.
Lets say it is a movie rental customer list that lists each rental, and column "A" is the customer number, column "B" declares if it was returned late.
I need to compile a list of "all" rentals by customers who have "EVER" had a "late return" or a "YES" in column "B".
I need to find all account records/rows of accounts that at any time had a "YES" in column "B" even if the some or many of that customers rentals/entries have "NO" in column "B"
so say: ...
I have to scrub files of 20,000 phones numbers against a file of several million phone numbers on the national do not call list.
On sheet one I have all 20,0000 phone number and then on sheet two in 5 columns I have roughly 2 million phone numbers. I need to know if any of the 20,000 phone numbers are in the 2 million on sheet two.
Right now I am simply using a vlookup formula but it is taking a very long time to update all of the fields.
I have a spreadsheet with 7000 lines exported from a database. I'm looking for lines that exist with an @@2 that don't have a corresponding @@1. Let me explain.
SV10000000@@1
SV10000000@@2
SV10101000@@2
I want to keep the first two lines because there is an @@1 associated with an @@2. I'm looking to single out and delete lines that have @@2 that don't have a corresponding @@1 associated with it.
Hello everyone....
I have a project where I need items for different boxes.
i have 20 boxes that need the same amount of items. However, when I came towards the end, i ran out of items.
For example
BOX A IS MISSING
ITEM 1 AND 2
BOX B IS MISSING
ITEM 2 AND 5
BOX C IS MISSING
ITEM 1 AND 5
I have all in a spreadsheet all the items that are missing per box. Here comes the main question....
How do I program my spreadsheet find the items that are missing in each box and summarize in another sheet?
The summary I am looking for is....
ITEM 1 - 5 (MISSING)
ITEM 2 - 9 (MISSING)
and so on...
I started doing the code, but I haven't got too far.
I have two lists of numbers.
The first list contains site numbers of people who havent responded to me.
The second list is the master list of site numbers along with a column showing the date they responded.
Now, a site number is built like this:
123456/0001
123456/0002
So it is possible for the same 6 digits to appear more than once in the master list.
What I need to do is to compare the first 6 digits in the non responder list against the master list, because some sites, like the example above, may have more than one '0001' tag and so if they have responded to me from site '0002' I dont want to spam their other sites with emails.
I've tried using match and various formulas I've found from google etc, but nothing seems to work!
The goal of this is to get a list of non respondents that have not responded from any of their sites listed in the master list.
Non Responses
Master List
Date of Response
203289/0001
201162/0003
06/06/12
[code]...
I have a database with ~18000 rows and 29 columns. I would like to filter the data by duplicates in one column, based on total, but keep the remaining data in the row. For example: I have account numbers listed in one column, often duplicates. I can get the total in a pivot table no problem, but need the other data associated with that account. I do not need to see all accounts, only duplicates for accounts listed say greater than 5 times. The data in columns B+ are important.
I have a HUGE vlookup I created to paste in the pivot table data (account numbers and totals) to run a look-up based on those numbers, but I see that running into problems when you run 4k+ look-ups.
I want to see accounts listed only 5+ times, include that total (as in a pivot table) and the remaining 28 columns. I have tried to run this in a pivot table completely, but still too much data to process (plus all the subtotals that I have to keep removing).
I have over 400 thousand rows in my data-set and I'm trying to find duplicates using a countif or similar formula. Let's say I have the following:
Blue
Yellow
Green
Blue
Blue
Yellow
Pink
I need the formula to increment the count. So for example, the first time Blue appears, it equals 1, the second it appears, it equals 2, third time is 3 and so on.
And, I need it to actually work with 400 thousand rows. I have tried using the following formulas without success as Excel keeps crashing:
=IF((COUNTIF(T:T,$T2)>1),COUNTIF(T:T,$T2),"")=IF(COUNTIF(T:T,T2)>1,2,1)
That second one equals "2" if there's more than 1, which will work for me as well. Better would be if it increments though.
The purpose of having a count is so that I can pivot the data, then filter out all the "1"s and report on it. Therefore I can't use conditional formatting, or simply using Excels Remove Duplicates feature. I need the same data-set for other pivots and charts.
I have a list of text values in column X. I need to come up with a formula in column Y.
X
Y (RESULTS OF REQUIRED FORMULA)
Comment
1
HAT
1
First HAT in column
2
[Code]....
I can't play about with the natural order of the spreadsheet, so there's no chance I can re-sort the data into column X and (easily) identify the duplicates that way. So, it could be that the duplicated value(s) will appear in any cell within that column.
I need to identify whether the item is a duplicate in the unsorted list. Ideally, the first entry of a set of duplicates will be given 1, then the subsequent duplicates themselves given a 0 (zero). It's to subsequently do some counts on.
I guess that as long as the one of the entries in the duplicates is marked with a 1, while the others are 0 (zero), that's all that's important.
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 RelatedI have many rows of data. How can I find duplicate cells in a column?
View 9 Replies View Relatedi have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).
At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.
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)
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
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 RelatedI 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?
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
I have columns from A to AA and need to count the duplicates in at least 2 columns and accordingly need to give them values on the basis of their ratings (A, B, C, D) and type of work (book, article etc) given in yet other columns.
I tried COUNTIF, but that does not work for multiple perimeters
I tried SUMPRODUCT, =SUMPRODUCT(($G$4:$G$3000=$G28)*($AA$4:$AA$3000=AA28)), but that only gives a multiplication of my input without the possibility of giving new values to the output.
I tried AND(IF( but that did not want to work either really...
I think the solution is ifsum, but I do not know how to use it in this scenario...
I know this all sounds a bit blurry, so let me attempt to make it more concrete.
Author Title Year Rating Type Desired outcome
P Marx I 2005 I A I Book I duplicate
A Hegel I 1923 I B I Article I -
B Kant I 1674 I D I Book I -
A Derrida I 2005 I D I Monograph............................
I have got a very big list in worksheet "1" which contain of only one column, the column D. There are over 150 000 cells in column D and each cell contains of many members seperated by ";". Some cells in column D have many dozen members. Now i want to generate two new columns out of D in another worksheet "2". The two new columns in "2" should be the colulmns A and B.
View 14 Replies View RelatedI want to search Column A in sheet1 vs column B in sheet 2. If there is a match, i want copy Column B in sheet 1 and PASTE it to Column E in sheet2. I have a macro (helped by those on here ) that will find the duplicates and copy cells from the cooresponding row only to another sheet.
The tricky thing is, the macro I have makes a complete new sheet. I want to copy the data to column E on sheet2 ONLY if there is a match for that cell. If ther eis nto a match i dont want it to put anything.
I have two columns of B2 and C2 data in the same worksheet and want to identify duplicates in the columns and return a value of true or false in column D. The formula I have been utilizing is:
=IF(ISNA(VLOOKUP(B2,$C$2:$C$5000,2,FALSE)),0,VLOOKUP(B2,$C$2:$C$5000,2,FALSE))
However, i receive a #REF# return in D. What am I missing ?
sorting duplicate e-mails across three columns in an Excel spreadsheet.
Precisely, I have three mailing lists (Column A, B, and C) that I would like to sort.
I would like to know what e-mail addresses appear in more than one Column (Mailing List), and I would like to highlight/flag them somehow.
I have attached an example spreadhseet that contains fake e-mail addresses for test purposes. As you can see, some e-mail addresses are duplicated or in triplicate across the 3 Columns. In other cases, an e-mail address may be unique to a specific Column.
In my real spreadsheet, I have approximately 3,500 rows and 3 columns.
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.
I have a list of line items and I want to be able to see how many duplicates in Column A and B.
Column A has text field and column B is a numeric field
I thought I could use INDEX MATCH but I'm unsure.
I would like to use the sumproduct or countif formula for a set of data. I have looked around the forum and have not found what I need so
Column A______Column B
West___________1
West___________2
West___________1
West___________2
South__________ 1
South__________2
South__________1
South__________2
I want to count column B for all "West" (column A) and I don't want duplicates. So it would count two unique characters for West and two unique characters for South. I want "west" and "south" separated.
