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)
So here I have two columns Column 1 with repetitive values of some 10000 records Column 2 with unique values of 100 records I want to compare Column 1 and Column 2, identify the values in Column 1 that match with Column 2 and highlight them. Conditional formatting for duplicate values doesnt work because Column 1 has repetitive values.
View 6 Replies View RelatedI have two columns of data. Each row cell is 27 characters long. I want to
find any duplicate matches between the two columns on just the 1st 9
characters of each cell. Is there a function that can do this?
I have column A that is 1,500 records.
I have column B with 40,000 records.
Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.
My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records?
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.
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.
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
My Process goes like this: We receive work in the excel sheet and in the column K(Period will be in YYYYMMDD)
First I want macros by which I can change YYYYMMDD TO MMDDYY.
then
It should compare column J and column K.
see the below example.
Period date (column K) Product end date (column L)
Column J column K
20131031 10/31/2013 = If column J & K dates are equal then "No action required"
20131031 7/31/2013 = If column J is higher then column K dates then "Action required"
20130930 11/2/2013 = If column J is lesser then column K dates then "No action required."
I need comments in column Q.
macro to compare 4 columns of text (first and last names) then add a checkmark in separate columns if they match and don't match?
For example I have this kind of data to compare and the results required:
Sheet 1
Last Name First Name
Smith Mike
Johnson Bruce
Hendrick Fred
Shaffer Kerry
Sheet 2
Last Name First Name Match No Match
Klee Pierre X
Verge Kerry X
Smith Mike X
Wright David X
Hendrick Fred X
I need the macro to mark an X in the Match column if the first and last names match only and if they do not match exactly a X in the No Match column.
I have account numbers in Col A and in Col D .I would like a macro to highlight the color the items in Col D that are not in col D. Blanks can be ignored
View 3 Replies View RelatedI need making a macro which compares a particular range of columns in one sheet to other sheet in same workbook.
It is like i have two worksheets named "Working Copy" and "Cleaned Data - (Working)". In this I have to compare Columns (A:AG) from Working Copy to columns (B:BH) of Cleaned Data - (Working). and if there is any difference between any cell then it should be highlighted with a colour in both the worksheets.
I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.
View 8 Replies View RelatedI am trying to modify the macro listed below for the following example. It would work when I have only numerals in the cell but this new query, the cell has both letters and numbers.
I want to compare column "M" from worksheet one to column "B" in worksheet two. If the information matches, then copy the value adjacent from worksheet two column "A" to worksheet one column "L".
Here is the macro that worked for me using a search of only numerals.
figuring out why the data doesn't copy?
Macro:
Sub merge_accession_PS_rad_productivity()
Dim rng2 As Range, c2 As Range, cfind As Range
Dim x, y
With Worksheets("Imaging_Summary")
'N4=Accession on Imaging Summary worksheet
[Code]....
I am trying to create a macro that will compare several un-formatted workbooks and copy just what I want to a workbook of my one.
I start by having 2 different workbooks opened ("Main" and "Change") side by side like this: 1.jpg
Then i run the macro in my "Main" workbook and it should appear an Userform like it showed in the image below: 2.jpg
This is so that i can manually fill the user form with the correct columns by looking at the excel file, for example:
Ref=E3;City=G3; Data=I3.
Pressing Ok, it fill the "Main" workbook with the matching information so it and closes the "Change" workbook.
At the moment my code is in this stage:
VB:
Private Sub CommandButton1_Click()
myfile = "C:Userssst1brgDesktopRelatorios" & TextBox1.Value
Application.Workbooks.Open Filename:=myfile
Windows.Arrange ArrangeStyle:=xlVertical
[Code] .....
But I am stuck because there isn't any error and nothing happens.
2014-07-30_16-52-23.png
Fist I want to compare ColB=ColH, if TRUE, compare colA=colG, if TRUE, again compare colC=colI, IF all this conditions true, then give (colD-colJ) on colL.
all the unmatched rows in 2 tables to populate with different for each table
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
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 have file with so many columns and i want to keep only columns i want.
Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb
expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
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 ?
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 View Relatedsorting 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.
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 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.