Free Microsoft Excel 2013 Quick Reference

Compare two Columns to find missing Items

Hi guys, I have not used Excel in a long time and I forgot how to compare two different columns and highlight or extract the items that are presentin one of the columns but not in the other one.

Thanks in advance...


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:
A........................B.................C
procedure1...........................225-3
........................ 25-1...........22-2
.........................33 .............457
procedure2...........................33-55
.......................35-12...........25-6
.......................12-8.............25-1
procedure3...........................33-89
.....

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).

I hope I explained this well - is there an excel function for this, or a macro that could be made to do this? Thanks!

Hi,

I have searched this forum for my problem, found many similar problems but not the same.
I have two columns A and B. Column A - 500 values, column B - 350, more or less, values that are already in column A.
I need to find which values from column A are NOT listed in column B.

Thanks in advance

Problem: I work with two spreadsheets daily in which I need to compare two
columns and extract, note or highlight differences. First worksheet has that
days sales order numbers, which is then sent to our warehouse for shipping.
Second spreadsheet is sent back from warehouse with sales order numbers and
their respective tracking numbers.

Issue: Occassionaly sales orders slip through and don't get shipped. I need
to compare both spreadsheets and find the sales order numbers on the 1st
sheet that don't appear on the 2nd sheet and therefore, didn't ship.

Can anyone spell UNHAPPY CUSTOMER?

Solution: Would like a command or formula that will compare sales order
colums in both spreadsheets and extract those numbers that didn't ship to a
3rd spreadsheet.

Thanks,

Toby

Hi All,

I'm looking to compare two columns and then put the duplicate names from those columns into a third column. Can this be done? Thanks in advance!

I regularly need to compare columns of data (filenames / peoples names etc etc.) and find the missing cells. Has anyone seen any sexy VBA code or a Macro to Sort the columns and then shift the cells in either column until there is an exact match. See screenshot below for a visual of what I'm trying to do.

The Yellow cells are optional just to draw the user to the gaps.

Thanks,
Ger

Hi all

Pretty new to excel, and attempting to compare two columns of data to
find the common values.

e.g I have the following 2 columns of data

column1 column2
a b
b c
c e
d g
e x
f y
g z

what I am trying to do is from these 2 columns, determine whthe list of

1. Common values
2. Values that appear in column 1 but not in column 2
3. Values that appear in column 2 but not in column 1

The data is provided from a database. I tired using the 'IF' function
but I could not work out how to say "if the value of a column 1 cell
*is in the whole range* column 2".

Using The help section in excel, i could only work out how to do this
as the examples only compare one value against another, not against a
range.

any ideas?
thanks

--
patman
------------------------------------------------------------------------
patman's Profile: http://www.excelforum.com/member.php...o&userid=36739
View this thread: http://www.excelforum.com/showthread...hreadid=564605

Hi all

Pretty new to excel, and attempting to compare two columns of data to find the common values.

e.g I have the following 2 columns of data

column1 column2
a b
b c
c e
d g
e x
f y
g z

what I am trying to do is from these 2 columns, determine whthe list of

1. Common values
2. Values that appear in column 1 but not in column 2
3. Values that appear in column 2 but not in column 1

The data is provided from a database. I tired using the 'IF' function but I could not work out how to say "if the value of a column 1 cell is in the whole range column 2".

Using The help section in excel, i could only work out how to do this as the examples only compare one value against another, not against a range.

any ideas?
thanks

Hey all

I want to create a macro that will allow me to compare two columns of data from 2 different worksheets and then if it finds a match creates a new column in the 2nd worksheet containing the value from the 2nd column in the first worksheet which corresponds to the first matching column. For Example

Worksheet 1 Worksheet 2

Column A Column B Column A New Column
On 1 Off 2
Off 2 Reset 3
Reset 3 On 1

So I kinda tried to create a graphical representation of what I want it to look like. Basically I want to compare Column A of worksheet1 with Column B of worksheet 2, with Column A from worksheet 2 not necessarily in the same order as Column A in worksheet 1. Column B in worksheet 1 is the corresponding numerical value for the text in Column A. I would like the macro to compare both Column As and if there is a match return the corresponding value in Column B to a New Column in worksheet 2. I'm kinda new at VB, so any help with be great.

Hi,
I have this problem, What i have on hand is two column of data
containing part number of two different devices. What i am trying to do
is to compare this two column and find out all the part number that is
common to both device and also which part number is unique. And
organise and display this in excel. Anyone know any way to accomplish
it please help.

--
kuansheng
------------------------------------------------------------------------
kuansheng's Profile: http://www.excelforum.com/member.php...o&userid=30658
View this thread: http://www.excelforum.com/showthread...hreadid=503578

Hello All Excel Guru's:

I was wondering if it was possible to have a macro to compare two
columns of data and in the third column only show the ones that are in
Column B and not in Column A and vice versa, In Column A but not in
Column B in Column D

Examle:

Column A Column B Column C Column D

Apple Apple
Bananas Bananas Strawberry
Orange Pear Pear
Grapes Orange Cherry
Strawberry Grapes
Cherry Plums Plums

I have a formula that does this, but I have to copy this formula down
Column C. The
formula that I have is listed below:

formula located in Column C
=IF(B3:B101="","",IF(ISERROR(VLOOKUP(B3,$A$1:$A$20 00,1,FALSE)),B3,""))

formula located in Column D
=IF(A3:A101="","",IF(ISERROR(VLOOKUP(A3,$B$1:$B$20 00,1,FALSE)),A3,""))

Is there anyway to have a macro do the same thing, except that have
all the items in column C and column D sorted together?

Any and all help in this matter is greaty appreciated.

Argus

I have two columns of siganl names. Column A is "From" data. Column B is "To"
data. In some cases the dulpicates are the same signal path in reverse. An
example would be Col A: Pin_1 Col B: GND. Further down the list would be Col
A: GND Col B: Pin_1. I have 60,000+ rows of signal names. It would be great
to find the duplicates and eliminate doing twice the work.

Thanks
Dan

Hello All Excel Guru's:

I was wondering if it was possible to have a macro to compare two
columns of data and in the third column only show the ones that are in
Column B and not in Column A and vice versa, In Column A but not in
Column B in Column D

Examle:

Column A Column B Column C Column D

Apple Apple
Bananas Bananas Strawberry
Orange Pear Pear
Grapes Orange Cherry
Strawberry Grapes
Cherry Plums Plums

I have a formula that does this, but I have to copy this formula down
Column C. The
formula that I have is listed below:

formula located in Column C
=IF(B3:B101="","",IF(ISERROR(VLOOKUP(B3,$A$1:$A$2000,1,FALSE)),B3,""))

formula located in Column D
=IF(A3:A101="","",IF(ISERROR(VLOOKUP(A3,$B$1:$B$2000,1,FALSE)),A3,""))

Is there anyway to have a macro do the same thing, except that have
all the items in column C and column D sorted together?

Any and all help in this matter is greaty appreciated.

Argus

Hi all,

This is my first post here and I'm real excited to be part of this forum. The reason I even found this forum is - I've been tasked by my VP of Sales to take two different spreadsheets, lets call them:

Sheet A - a database of potential clients downloaded from Hoovers
Sheet B - a database downloaded from an email list vendor

Sheet A is the primary database and holds all the information for each prospect, except for email address. Hence he bought and downloaded Sheet B and now wants me to merge the two. I realized that the common column in the two is "Phone Number" and so using the phone number I need to find compare all the records in both sheets and add the "email" field/column to Sheet A.

Is there a way to do this? I tried searching for "compare sheets", "merge sheets" on this forum and came across many vlookup, if, match commands and tried tweaking it but was unable to find a solution.

Please help!

Thanks alot in advance!

I have two columns of siganl names. Column A is "From" data. Column B is "To"
data. In some cases the dulpicates are the same signal path in reverse. An
example would be Col A: Pin_1 Col B: GND. Further down the list would be Col
A: GND Col B: Pin_1. I have 60,000+ rows of signal names. It would be great
to find the duplicates and eliminate doing twice the work.

Thanks
Dan

Hi,
I have this problem, What i have on hand is two column of data containing part number of two different devices. What i am trying to do is to compare this two column and find out all the part number that is common to both device and also which part number is unique. And organise and display this in excel. Anyone know any way to accomplish it please help.

Trying to figure out how to compare two columns of data (store#, date) to three columns on another tab (store#, date, Ticket#). I need to return the ticket value from third column to the match found in first tab. The length of columns do not match. There may be multiple dates that match and multiple store#'s that match.

I am trying to find duplication by comparing two lists. One list is a subset of the other. In other words, if they were side by side, they would not line up because one list may contain 15 rows where the other would contain 60.

Would I use a combination of the index and match function? If so, how would it look?

Thank

Hi Everyone,

I am trying to find a way to compare two columns of numbers. Each column contains about 2,500 rows of numbers and I want to know what numbers appear in one column and not the other.

Ex:

Column A Column B

1234 3424
4324 4324
5435 5345
6564 1234
3242 6546
5435 7657

I want to know two things:

1. What numbers appear in Column A and not in Column B.
2. What numbers appear in Column B and not Column A.

Is there anything out there that can do this? Most things I have found so far compare the rows side by side. I dont care about the order the numbers are in.

Thanks

hello again folks-

i am using excel 2000. i have two columns (different lengths) with usernames. i want to compare the columns and find the duplicate entries, putting them in a third column.

seems like excel can do this...it's just ME that's preventing it from happening

thanks as always!!!!!

We receive an invoice from a 3rd party vendor. The invoice has two columns, Col A - Invoice Number and Col B - Total. I then run a sql query that will give me a range of invoice numbers for a given period along with the estimated total. I need to be able to compare both the invoice number with the associated total. I have a formula that will make sure that I have every invoice but I don't know how to check the invoice total. Current formula: =IF(ISERROR(MATCH(A2,$E$1:$E$11300,0)),"",A2). This lets me verify I have all invoice from Col. A to all the invoices I queried in SQL and placed in Col. D and now need to be able to compare the associated total with our estimated total. I thought I might be able to compare the totals, but since we have an estimated total, it is not exact. If more information is needed, please let me know.
I'm working on WinXP and using excel 2003.

Thanks,

Hi All,

I'm not sure if this is possible but what I would like to do is compare two columns for a best match.

The set of columns contain names from two different sources. They are not an exact perfect match so vlookup or match will miss out names that are virtually the same but not an exact match (according to Excel). I could also use the vlookup with the true parameter (with columns sorted) but this never even approximates a good match.

Is there are way that I can compare two columns and find out how close the match is in Excel? For example:

The names A J Johnson and Andrew J Johnson are the same peoples but trying to cross match with Excel is difficult. What I would like Excel to output is Andrew J Johnson being the closest match to A J Johnson (which Excel doesn't return). Or is Access the best for doing something like this? I've racked my head about this...

Rgds,

CS

I have two columns, 1 with 3800 entries and 2 with 1500 entries. I want to
find the entries which are in column 1 but not in column 2.
--
kpk

In a column, serial numbers are typed as a reference. For example from 1 to
173. How ever actual number of raws is 175 (obvious by checking raw numbers).
2 numbers are missing. Let say for example 17 and 56 are not typed.
What is the easiest way to find these raws?
Is there any written MAcro, to find these raws?
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

A Macro to find missing serial numbers in a column

Please help...

I have a really big spreadsheet and would like to find all the unmatched
information when comparing two columns in a spreadsheet. Is there any
formula or functions to perform this checking, or is there any methods
suggested?

Example:
Info listed In column A: red, blue, green
Info listed in column B: blue, violet, black, yellow
Result (unmatched info) after comparing column B to column A: violet, black
and yellow