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

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

- Comparing two columns of data to find common values
- Macro to compare two columns of data
- Macro to compare two columns of data
- Excel VBA - Comparing two columns of data
- Compare two columns of data for same results (same piece of text in cells)
- How to compare two column of data and sort them out according
- How to compare two column of data and sort them out according
- Comparing columns of data
- Combining two columns of data to create new series
- Comparing two cells of data to produce a comment
- Need to find specific values in range using loop
- VB Macro to compare two columns from different worksheets and return a 3rd column of values
- Compare two columns from different tabs return value from third col of 2nd sht to 1st
- Excel Formula or tool to compare two columns of data
- Comparing two columns of data that are near identical
- Excel Formula or tool to compare two columns of data
- MATCH formula - better way to compare 2 columns of data?
- Formulas: Need to compare 3 columns of data with sp
- Comparing columns of data
- Help comparing two sets od data to find the odd data
- comparing two columns of data
- Need help building macro to auto sort two columns of data
- Comparing Two Columns of Numbers
- Convert to columns of data to single column

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

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 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 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?

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

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

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.

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

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

Attached workbook might explain better.

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!

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.

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?

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.

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?

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

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

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

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

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

Much thanks for all your help.

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 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"