Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Compare 2 Columns To Find Unmatched Values

Is there a function to compare 2 columns to find all of the values from one column that are not in the other column? Basically my data looks like this:
........................ 25-1...........22-2
.........................33 .............457

I have another list of data (just the numbers like columnB) that I want to put in column C and see what is in column C that isn't in column B. The numbers will be arranged in a different order, and there will be data in column B that isn't in column C. What I need to do is find out what is in column C that isn't in column B so that I can go back and define what procedure they fit into (that is a seperate process).

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Compare Values In 2 Columns And Find Corresponding Value In 3rd
I have gone through various posts but am unable to get the answer. What I'm trying to do is find if a value (text or number) in Col. A exists anywhere in Col C. and if it does, find the corresponding entry in Col. D and paste it back against the value of Col. A in Col. B. To explain better I have attached a sample excel file Ex1. In the file, the entry "sun" in Col A is present in Col C and the corresponding value in Col D is 24 which should be pasted back against "sun" in Col B.

View Replies!   View Related
Compare And Move Matched/unmatched Cells
xxx xxxx xxx 123 123
xxx xxxx xxx 123A 123B
xxx xxxx xxx 123B 123C

Is it possible to leave column A, B, C, D untouched and match against column E from column D?

If E matches D then leave in the same row and if E does not match D then move to column F? The identical numbers may be included in the columns, but different rows.

I have a set of transactions from one system which is column D and I have another set of transactions from another system which may or may not have the same transaction from column D. They are all sorted from + to -. I just need to match up cells from column E to column D.

View Replies!   View Related
Compare Two Workbooks And Copy Unmatched Data
I have two workbooks: one is a daily workbook that will be used to keep track of work accomplished, the other is a weekly report that is generated by head office.

What I need to do each week when the head office report is generated is to match up the Project ID's (they are the constant in each workbook) from the daily workbook with the weekly one. If there are Project ID's that are new, the corresponding information would then be copied over to the daily workbook.

I am working on the copy command but I'm just not sure how to go about setting up the search to match Project ID's.

View Replies!   View Related
Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)
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

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.

View Replies!   View Related
Find, Compare And Delete From 2 Columns
I an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is not found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if not found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result will be the same cell value in Column A and B. I need VBA code.

View Replies!   View Related
Find In 2 Columns, Compare And Delete
I an using Excel 2003. In that i am having 2 columns (A&B). I need to take each value from Column A and search that cell value in Column B. If it is found in Column B then delete the cell data in Column A. Then take the next cell data in Column A. Find operation and delete if found. Do it for entire rows in column A. Then repeat that for Column B also. So my final result contains uncommon cell value in Column A and B. I need VBA code.

View Replies!   View Related
Compare Values In Two Of The Columns
I have a worksheet where I want to compare values in two of the columns.
In column B there are EAN codes consisting of 13 digits.
In column E there are 5-digit numbers.

What I want to do is to compare the 5 digit number with the EAN code where these digits should be in position 7-12 in the EAN code. Those numbers in column E that doesn't have a match in column B should be marked (for example that the background color of that cell is set to "red").

XXXXXX12345X 12345 (match - do nothing)
XXXXXX12346X 12346 (match - do nothing)
XXXXXX12348X 12347 (no match - set background color to red)
XXXXXX12349X 12348 (match - do nothing)
XXXXXX12350X 12349 (match - do nothing)
XXXXXX12351X 12352 (no match - set background color to red)

There are more values in column E than B.

In all there are close to 4500 rows so I really could use a nifty macro to do this job.

View Replies!   View Related
Compare Values In 2 Columns To Another
I have two columns with numbers. For every row I want to find out if either of the numbers in the two columns is in a third column that I have with numbers. Does anyone know hoe to write a formula for this?

View Replies!   View Related
Compare 2 Different Columns Of Data - Find Unique Results
I would like to compare some keyword lists if possible.

I have a large list of Unique keywords in Col A (From A3),

This Column is called Keyword List A - Large"

I then have a keyword list in Column C (From Cell C3),,

This Column is called "Keyword List B - Small.

I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.

What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")

If possible could a pop up box appear saying
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz

I'm running windows XP and Excel 2007.

View Replies!   View Related
Compare Two Columns And Show Only Non Matching Values
Basically I have Two Columns.

Column A: Column B:

GN0001 DB0002
DB0002 DP0012
GE0025 GN0001
GR0026 GE0025
DR1235 GR0026
DP0012 DR1235

Column A is the master codes list for a unique product.

Now every time a product is sold, its unique code number is entered into column B.

What I want excel to do is compare Column A and Column B and show me only the codes that DO NOT match.

This way I know which items should be present in store when I do a physical stock check.

So the end result should be:

Column A: Column B: Column C:

GN0001 DB0002 GS0025
DB0002 DP0012 PC128
GE0025 GN0001
GR0026 GE0025
DR1235 GR0026
DP0012 DR1235

View Replies!   View Related
Compare 2 Columns & Place Unique Values In A 3rd
I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.

View Replies!   View Related
Compare Values In Multiple Columns And Return A Result
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.

I have a column between the years that calculates the percent of gross revenue for the specific department/cost.

I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.

The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.

View Replies!   View Related
Compare 2 Columns Delete Rows With Matching Values
I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.

View Replies!   View Related
Find Values In 2 Columns And Return A Value In A 3rd Column
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message

View Replies!   View Related
Find Maximum In Mutiple Columns & Delete All Values After
writing a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column.

Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.

View Replies!   View Related
Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D
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 Replies!   View Related
Add Row On Unmatched Condition
I have a summary list of all projects that my employees work on over the course of the month. Its gets sorted by date and project and is a pretty extensive list.

My goal is search through the list and add a blank row between each project.

I have written this code, that does work, but it takes quite some time to execute and I know there has to be a better way.

Can you guys take a look at it and see if there is more efficient way to do this?

Dim myarray As Variant
Dim cell As Range
Dim mycount As Integer
Dim i As Integer

Set myarray = Worksheets("summary").Range(Range("b2"), Range("B65536").End(xlUp))
Set cell = Range("b2")
mycount = myarray.Count

For i = 0 To mycount
If cell.Offset(i, 0).Value <> cell.Offset(i + 1, 0).Value Then
Range(cell.Offset(i + 1, -1), cell.Offset(i + 1, 3)).Insert shift:=xlDown
i = i + 1
mycount = mycount + 1
End If
Next i

View Replies!   View Related
Generating A Unmatched List
use a vlookup with an IF function to display those unmatched record found in sheet1.

In sheet2, there are some new and old firms. Thus I need to separate them to process.

View Replies!   View Related
Is It Possible To Create A List Of Unmatched Entries
I am using excel 2003 to count my inventory in the following manor:

I have a "database sheet" that is comprised of possible inventory SKU numbers.

I have a "scanned sheet" that lists all the SKU's that I have scanned in the warehouse

I have a "count sheet" that counts the number of times each SKU in the database appears in the "scanned sheet"

Am I going about this in the most efficient manor?

Question 2: Frequently, new Items appear in the warehouse that are not yet in my database. Is it possible to create a list of SKU numbers that do not have a match in the database? As it stands, any SKU numbers that are scanned but not already listed in my database - simply do not get counted.

View Replies!   View Related
Compare Columns
I have two diffrent sets of columns of data on a spreadsheet one column set is A,B the other is D,E I want a formula or makro that will check columns A,B and compare it to D,E and if it finds a match will paste it in G,H is that possible and how would I do this?

View Replies!   View Related
How To Compare 2 Columns
i've check the other threads on how to compare two columns but for some reason it is still not working for me.

I have 2 columns. Column A is the master column and column B is the comparing list. If the company that is listed in Column B (Anywhere in column B) matches exactly with the company name in column A, I would like it to say "check" in column C.

View Replies!   View Related
Compare Value In Columns
I have two worksheets but have a same column named "Admin No.". I will need to compare the 2 "admin no." column.


Worksheet 1
Admin No.Name
5555Ting Ting

Worksheet 2

Admin No.Name
5555Ting Ting

I will ike to compare both "admin no." column and when worksheet 1 5555 can be find in worksheet 2, it will return True. 6666 cannot find in worksheet 2 then it will return false.

View Replies!   View Related
Compare 2 Columns & Return In Third
I have a sheet with 2 big columns (3000 each). I want to search each element from the first column in the second column and put a message "Yes" if it is and "No" if it isn't, at the same position in the third column. How can i do that? I want to use the sheet for more than 3000 lines (about 5000). I attached an example.

View Replies!   View Related
Compare Columns Within A Range
i'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.

So in the file...
I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...

So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....

View Replies!   View Related
Vlookup - Compare Two Columns
I need a Vlookup to look at two columns when searching. here is an example:

Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).

Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...

currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)

this formula will pull the data in sheet two column 3 (production) for the employee that matches.

how can i make it compare first to col 1 (month) and then col 2 (employee).

View Replies!   View Related
Compare All Columns To First And Update
I receive hundreds of excels which I combine into one sheet for input into a program (which I don't know much about). The combined sheet contains 2 columns repeated with different information. For instance Column A is Category, Column B is Dollar Amount, Column C is Category,
Column D is Dollar Amount and so on.

I then start with Column A and compare it to Column C to make sure the categories match. If C is a new category (not listed in Column A) I shift columns A and B down one and add the new category to Column A. This updates my master list in column A but also maintain the format and correct dollar amount that was listed in Column B. Then I move to Column E and compare it to A using the same logic.

I repeat this process throughout hundreds of columns. I then delete all the category columns and am left with Column A as a master category list and all the dollar amounts in the correct location for all excels that have been sent in. I'm hoping someone here knows a faster way for me to do
this using VB or VBA for Excel or a Macro. I've tried a few things I've found on these forums to no avail, and I'm not an expert when it comes to this. This currently takes me days to complete, and I know it should not take this long. Please save my sanity!

View Replies!   View Related
Formula To Compare Columns If The Value Is Same
I have data in 2500 rows and 29 columns and need to compare cells in 5 columns if the value is same and make the font of cells that are same color red or make a different cell red if any value is not same.

I did this with a macro but it takes few minutes(3-4).
Now I try to do it with a formula but no idea how. Formula is faster.

View Replies!   View Related
Compare Data Between 4 Columns
i want to compare data between 4 columns, 2 columns of data is in Sheet 1 and 2 columns of data is in sheet2. Data starts at A5 in both worksheets. Data consists of stock codes and there holdings. Sheet 1 data is real data the primary source, sheet 2 data is a bit of real data and incorrect data.

My data in sheet 1 is layed out as below, this is just a sample, real data has around 1500 stock codes and holdings.

Starting at A5
Col A (Stock) Col B (Holdings)
5 aap 10500
6 aax 10987
7 aas 76544

Sheet 2 data is layed out as above but holdings may differ, so

Col A(Stock) Col B (Holdings)

5 aap 10500
6 aax 10300
7 aas 76534

I need a formula which will tell me which stocks holdings have changed in sheet 2 compared to sheet1.

View Replies!   View Related
Compare The Data Between The Two Columns
I'm working with Excel 2007, I have two tabs on my spreadsheet, each tab contains a column of numbers formatted like 5552220. Is there a way to run a macro or write a formula that would compare the data between the two columns and either automatically delete the numbers that don't have a match or highlight them? For example:

Tab A Tab B
5551112 5551112
6660001 6660001
1234567 1234567
2222222 2222222

6666445 needs to be deleted in tab B because it's not present in tab A. There could be up to 100 numbers that require deletion from tab B.

View Replies!   View Related
Compare Sheet And Columns
[data] ...

I have three sheet. named Sheet1, Sheet2 and Sheet3.
Sheet1 being the master sheet. What I want to achieve is:
I want to search for Name in sheet1 with the list of names in Sheet2,
If the name is found, then compare the Age in Sheet1 and Sheet2. If the age matches make an entry in Sheet3 "matched" else "didnt match".
If the name itself is not found on sheet2 , make an entry saying not found.
I am not sure what would be the best approach to solve the problem(formula/VB)...

View Replies!   View Related
Compare Columns Between Two Sheets
The code below compares values on sheet 4 column A to sheet 3 column A and then colors a cell "Green" (Temporary), later I will place data from sheet 4 into sheet 3.

I dont know why but when it finds data on sheet 4 that is not 100% numeric it errors out.

Run-time error '91'
Object variable or With block variable not set.

The data in sheet 4 column A is primarily numeric, there are and always will be some numeric/alpha strings.
I can change the value of sheet 4 A2 to "123x" from "123" and the code stops as described. Leaving sheet 4 A1 as 100% numeric, which works fine.

View Replies!   View Related
Compare Columns Of Data
I have column A with 228 rows and column D with 314 rows. Both columns have the same data except that D has different data. I would like to line up everything that is the same in A and D and everything else in column D that is not the same move to G. Is there a quick way to do this?

View Replies!   View Related
Compare Across Multiple Columns
I can find lots of ways to compare two lists with single columns, but I need a very basic guide to comparing multiple ones across two sets of data and then highlighting or extracting the unique records. I would prefer to keep the data in the existing columns for later sorting and other purposes.

I currently have the two datasets as two separate worksheets in an Excel 2003 file. The two datasets consist of the same three columns containing strings of text, but there is quite a difference in the number of rows. Dataset One is c. 3550 records, Dataset 2 is c. 1600. There are no duplicate records within each dataset, but there are duplications across the datasets and the records are in a different order, so it's not a matter of comparing Row 1 to Row 1 and so on. The data look a bit like this (semi-colons to show columns):

PM;Smith;Mary Anne
PM;Jones;Fred Henry
PD;Wilson;Peter John
PG;Green;William Laurence

View Replies!   View Related
Compare Two Columns And Return Value
I have two columns with the same data type (text) and I want to compare one column to another and if they match, return the data from another column.

For example: I have 2 columns of names on separate Excel files. One file contains name and phone numbers, while the other doesn't. I want to compare the names from file one with file two and if the names match, return the corresponding phone number in a new column.

I've tried VLOOKUP and IF statements, but I can't get it to come out.

View Replies!   View Related
Compare Data In Columns
I have 2 columns say A and B (infinite number of rows)
A is an Indexed number starting at 1
B is numerical data.

I now have a second set of the same columns with the same data, however some rows are missing. I need a way of comparing the data and then display the rows I am missing in the second set of columns.

For example the first set will be the master list and look like:
Index Data
1 34
2 46
3 54
4 276
5 311
6 89

Second list has row 2 and row 5 missing.

Index Data
1 34
3 54
4 276
6 89

The desired result is to have the missing rows displayed;

Index Data

2 46
5 311

View Replies!   View Related
Compare Columns From 2 Sheets
I'm trying to cmpare two excel spreadsheets, each one of then have 3 columns with column A being the KEY to lookup and compare records into another spreadsheet. So in my example spreadsheet If u take cell ZVNA!A1 and lookup for values in ZSKU!A* and find a match then comapre the B1 and C1 in respective ZSKU! Column B and C. If there is a record for A1 in ZVNA then comapre it's column B value with column B value of ZSKU, and column C value with column C value of ZSKU. I have not used macro/vba before so if you could tell me formula based solution that will be good or point me how to use macro. I did Vlookup but it did not return me 100% accurate result may be bec my format of column is not accurate.

View Replies!   View Related
Compare Text In 2 Different Columns
I am trying to find out what formula I would need to use in order to have two columns compared. If the two columns have the same information I want the information from column 3 to be place in another particular cell.

View Replies!   View Related
Compare Multiple Columns To Each Other
I would like to create an excel formula that will easily compare multiple columns to each other. For example

C1 C2 C3 C4
-- -- -- --

I want to create a formula which will compare these 4 columns to make sure they are all the same. If they are all the same, then TRUE else FALSE. I could compare C1 to C2,C3&C4, then C2 to C3&C4, then C3 to C4 but I am thinking there must be an easier way to do this.

View Replies!   View Related
Compare Two Columns With Names
There a list of names in columns A & B. How one can compare the names in column A to column B and put the results in column C to show only the names that are the same between columns A & B?

View Replies!   View Related
Compare Columns Between Worksheets
I have two worksheets, PM1DATA and AMDATA, i'm looking for a way to compare the two sheets and have any data that appears in collumn B within the AMDATA sheet, but NOT within collumn B of the PM1DATA sheet to be copied and moved to a sheet called NEWKITS. I would like the entire row to be copied when new data is found, not just collumn B.

View Replies!   View Related
Compare And Find
I am trying to find out if the articles in column A exist in column C. if yes, return the value of the same cell in column D, if not, return "no Match".

I know for a fact that there are money articles in column A do exist in column C, but I see the "no match" in the corresponding cell. for example, manuallyfin this article in column A13622356, and manually find it in column C. it is there, but the Vlook up returned "no match" value

View Replies!   View Related
Compare Two Columns, Text To Date
I have two columns, salesman and date. This workbook tracks quotes by salesman. I need to know how many quotes for each salesman by the month. So what I need to know is how quotes salesman 'a' had in January and February and so on. I have several worksheets in this workbook that are all formatted the same.

View Replies!   View Related
Compare 2 Different Columns-show Difference
I'm looking at trying to view 1 large keyword phrase list of about 40,000 phrases to another large list.

All I want is a macro, I can assign a button to. Click it and in a new sheet it shows only the rows of data that are different.

I have tried googling it.

I found 1 that looked very good (Not that I really know anything about coding, but I couldnt get it to work.

I'm using excel 2007.
As a note the link was here.

I might as well post the code,, but I just couldn't get it to work, shame, because it sounds like it could be what I was after, I'll post it now, but if anyone can help me with this that would be great

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
MsgBox "Can't compare multiple selections!", _
vbExclamation, "Compare Worksheet Ranges"
Exit Sub

View Replies!   View Related
VBA For Compare Two Columns And Show Differences In Another
I saw a great code which compared the values in two columns A and B, data such as A123 and then shows, in another column ie C , things in A and not in B and in another column ie D things in B and not in A.

View Replies!   View Related
Compare The 1st 9 Digits In Two Columns Looking For Duplicates
I 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?

View Replies!   View Related
Compare 2 Columns Then Give Count
I need a formula to look in column B and find all "jun" (or anything else that I put in there) THEN look in column J and find only all "d" (d is one of 3 choices there) that correspond to the "jun" then give me the count of the "d".

This is an example. With the formula I can do a variety of things to get info that I'd like to have.

View Replies!   View Related
Compare Columns And Move To Different Worksheet
how to write the following in a macro. The task is to have the macro compare both the SSN, amount, and date...if they match go to next row. If Mysoft side exists without a match on the OMNI side, then range for that A-D will be cut and moved to Mysoft Only worksheet and the cells below will be moved up....same holds true for the OMNI side.

View Replies!   View Related
Compare And Count Recurrances, Two Columns
I'm working with a large amount of data comparing the proximity of bank branches. I want to find out how many branches two banks have within the same zip code.

I have two columns, , A and B each with a list of 3000+zip codes.

What I want to do is count how many of the cells in Column A are Equal to Column B.

For example if I have the following two columns:
1 1
1 1
1 0
3 3
4 3
5 7
6 8

I would want to return the number 3 because Column A is equal to Column B 3 times(two times with 1, and 1 time with 3).

View Replies!   View Related
Compare Four Columns, Give Yes Or & No Answer
I have the attached worksheet with clients' names in four columns (A-D). The first names and last names must remain separate. I need one additional column that shows whether a full name (first and last) in C and D also appears in A and B.

Then one more column that shows how many times the name in C and D shows up, but only if it is also in A and B.

View Replies!   View Related
Compare 2 Columns From Diff Worksheets..
I'd like to compare 2 columns from different worksheets in the same workbook with a twist.

If any and all data from worksheet 2-column c matches any and all data from worksheet 1-column c, then go back to worksheet 2-column B on the same data matching row, copy the data from that cell and paste it into the same row of the data match in worksheet 1-column b and paste it.

View Replies!   View Related
Copyright 2005-08, All rights reserved