Free Microsoft Excel 2013 Quick Reference

comparing two columns of data to find common values

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


Post your answer or comment

comments powered by Disqus
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

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

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

I am using Excel 2002, and I am trying to create a macro to compare two columns of data and highlight or shade the cells or colorize any entry that is not in both columns.

I also read something about being able to put a button on the excel chart that runs the macro. Could someone help me with that too?

I would like to Compare two columns of data in order to identify in what rows the same results (same piece of text in cells) will appear. As shown below the same data (Service_Desk) is a part of the cell. In the case below row 3 and 4 will comply as Service_Desk appears in the same row in the two columns.

snt1-HDK-Service_Desk xxxx-TS2-ISDM_Support
teb1-HDK-Service_Desk snt1-OSS-On-Site_Support
vwc1-NL-HDK-Service_Desk snt1-HDK-Service_Desk
vwc1-NL-HDK-Service_Desk vwc1-NL-HDK-Service_Desk
xxxx-NL-TS2-Wintel_B1 xxxx-NL-TS2-Storage

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

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.

Is there an easy way to compare two columns of data to see what is common
between them. For instance, I wanted to compare the first column in each of
the three tabs to see which cells are identical between them

Hi,

I was thinking this would be really easy to do but can't think of how to do it.

I have two columns of data b4:b157 and C4:C157. What I want to do is create a new series in say Column D which combines the data in the following way:

D4 = b4
D5 = C4
D6 = B5
D7 = V5
D8 = B6
D9 = C6

So in Column D I end up with a series twice as long of that in columns B and C. Obviously I could type in the formulas above but for 153 that would take too long!

Hope someone can suggest an idea.

Thanks,

Elvis

Hi, i really would appreciate if anyone can help me. is it possible to compare two sets of data (can be numerica or alpha) and produce a different outcome comments.

Attached workbook might explain better.

Hey everyone. I am currently trying to sort through several rows and columns of data to find specific values which meet the criteria I have inserted into my present code. I have several columns of data which contain for individual graphs. As of now the code is able to loop through the Column A and find the numbers which meet the criteria, and then insert them into either a new column or a new sheet. What I would like for the code to do is sort through each column, find all the numbers that meet the criteria, and then place all of those numbers into one column in a new spreadsheet. The code I have written is basically a peak-finding program that is as follows:

Sub FindMaxForce()

b = 1

For Row = 1 To 10000

Set CellBefore = Worksheets("Sheet1").Range("A1").Offset(Row - 1, 0)
Set CellMid = Worksheets("Sheet1").Range("A1").Offset(Row, 0)
Set CellAfter = Worksheets("Sheet1").Range("A1").Offset(Row + 1, 0)

If CellMid = "" Then Exit Sub
If CellMid >= CellBefore And CellMid >= CellAfter And CellMid >= 30 Then
Cells(b, 3) = CellMid
b = b + 1
CellBefore.Activate

End If

Next Row

End Sub

I am grateful for any suggestions anyone out there might have for me. 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.

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 looking for a formula in excel that will compare to columns of data and
put a result in another column. Basically: "If the number in column A (each
row will have a different number) appears in column E (any row and may even
have multiple occurences), put a Y in column G on the row in which the column
A number appears." Does that even make sense?

Hello

I need some help, I have two sets of data that I can't do an advanced filter on.

They are basically the same but have different endings so to speak.

Column A
Shaun -10.00.52110-EN-1.0.0
Clippos10.00
Column B
Shaun 10
Clippos 10

I would like to be able do a sort of advanced filter to find matches or possible matches in a automatic way.

Also I would like to do this on a Column as well where I might have the same data but just prefixed with a ABC or something.

Column A
ShaunSoftware -10.00.52110-EN-1.0.0
ABC Shaun Software -10.00

The problem with these prefixes or appendages are they are random and are different lengths.

Any help most appreciated.

I am looking for a formula in excel that will compare to columns of data and
put a result in another column. Basically: "If the number in column A (each
row will have a different number) appears in column E (any row and may even
have multiple occurences), put a Y in column G on the row in which the column
A number appears." Does that even make sense?

Hey all,

I have two columns of data that I am trying to compare (for instance, a credit card statement expense column and a Quickbooks credit card expense column). My goal is to be able to see pretty easily which cells either did not import into Quickbooks or were imported into Quickbooks and should not have been there (i.e. which cells don't match the other column). I have been using the MATCH formula (see attached xls), but this is still a manual process because after running it for each column of data, I then have to manually add all the columns that didn't match. Also, this ignores the fact that two $50 expenses are not the same exact charge. So, if I have 3 credit card transactions that are 49.48, the MATCH formula would tell me that, as long as 1 of the transactions is in Quickbooks, then all are in Quickbooks.
Anyways, sorry for rambling, but there MUST be a better way.. Right?!
Thanks in advance, and happy to help with anybody else's quandaries.

sample_match.xlsm

I need to compare three columns of data.

Column A = Executive Name
Column K = Resource Type (Employee, Contractor, Vacant, etc)
Column X = State (AL, CA, FL, etc)

I need a count of the number of vacancies working for Executive Smith that are in a set of States.

What I've tried repeatedly is an Array. I know you discourage this but I'm under a deadline....surprise ;-)

My formula(s) have looked something like this:

{SUM=(('Sheet Name'!$A$2:$A$2000="Smith")*('Sheet Name'!$K$2:$K$2000="Vacant")*(............

I've tried OR functions at the end, no luck.
I've tried creating a new column on another sheet that performed an IF
statement that created a new Column that represented the set of States I
need, but still no luck.

I've attached a very small sample of the file I'm using. Should give you an idea of the formatting and data.

Thank you,

Aaron

I have a feeling this is very simple, and I still can't figure it out

Is it possible to compare two columns of data in Excel. For example if I
have two columns of names A & B, with column A containing 5,000 names and
column B containing 1,000 names, how would I compare B to A. Additionally,
would it be possible to then conditionally format the duplicated item in
Column B, maybe make it bold or red?

Thanks

Howard

hello i have two sets of data and i need to find the data that is not listed
in the other data here some data i got
set1 set2
2865 326
3425 329
3459 332
chocp 720
Delay 3425
gwjennahb1 3459
gwlo785 chocp
gwpd900001 Delay
gwpd900002 gwjennahb1
gwpd900005 gwlo785
gwpd900007 gwpd900001
gwpd900008 gwpd900002

and i want to know how to get excel to show the data that is not listed in
the other set like this

set1 set2
2865 326
gwpd900005 329
gwpd900007 332
gwpd900008 720
how can i do it plz help

i need to take one column (file A) of data and find matching data in another column (file B).

how do i do that? i have at least several thousand cells of data in each column.

Hi, I have a worksheet and I'm trying to create a macro that will automatically sort two columns of data. I have attached a version of the worksheet I am using with the data wiped out and replaced with random information for privacy purposes. I'm looking for a macro that will auto sort ranges AO:AQ and AS:AU, which I am using to populate the two graphs at the bottom of the page. I want these columns to update any time the numbers change. (If it matters, the numbers in the original worksheet are driven by formulas, as opposed to this version where I just copied and pasted random values.) I am looking to sort (in descending order) the values in yellow columns AP and AT. There are headers in row 1. Any help you can provide would be MOST appreciated. I've spent hours trying to tweak various code snippets from the macro recorder, forums and websites, and have not found anything that works for me. As is probably obvious, I am a very novice VBA user, so the closer you can get me to formulating something that works without a lot of editting, the more helpful it will be.

Much thanks for all your help.

I want Excel to compare two columns of numbers. Column R has 1000 entries,
beginning with the number 1 and ending with the number 1000. Column S will
have from approx. two hundred to as many as six or seven hundred entries,
looking something like this: 3, 5, 6, 10, 11, 13, 14, 15, 18 . . . . . . 990, 992,
995, 996, 999, 1000. I want Excel to compare column S with column R and
display the difference in Column T. Column T will therefore look like: 1,2,4,
7, 8, 9, 12, 16, 17, 19 . . . . . 899, 991, 993, 994, 997, 998. I have to do
this multiple times, does Excel have a built-in function(s) that can do this or
do I need to write a macro? The numbers can be formatted as text if neces-
sary. Anyone have an idea?

I have a spreadsheet with two Columns of data as below.I need to combine the two into one column so that I'd end up with one cell containing both sets of numbers. Thanks for the Help.

I now have two columns:
Column A Column B
30° 58' 31" 88° 8' 51"

Need to convert to this:
Column A
30° 58' 31" 88° 8' 51"


No luck finding an answer? You could always try Google.