Compare two columns

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• May 4, 2005

• #2

Tugal,
Have a look at this post.

http://www.mrexcel.com/board2/viewtopic.php?t=132434&highlight=compare+data+two+columns

Colin.

 

Upvote 0

T

tugal

New Member

Joined

May 4, 2005

Messages

12

• May 4, 2005

• #3

That is close but not quite it. Any other ideas?

 

Upvote 0

T

tbird

Board Regular

Joined

Oct 21, 2002

Messages

184

• May 4, 2005

• #4

Not sure what difference there is when comparing col A & B as you have described it. Can you provide an example of data and what you want the result to look like? Use Colo's HTMLmaker utility (see note at top of this forum).

 

Upvote 0

T

tugal

New Member

Joined

May 4, 2005

Messages

12

• May 4, 2005

• #5

Maybe this will help

Book1
	A	B	C	D
1	List 1	List 2	Return
2	a	a	a
3	b	b	b
4	c	c	c
5	d	w	d
6	e	x
7	f	d
8	g	z
Sheet1

Thanks

 

Upvote 0

T

tbird

Board Regular

Joined

Oct 21, 2002

Messages

184

• May 4, 2005

• #6

So what you have described is that as long as columns A & B are the same it doesn't matter which you return in Col C but if they are different then you want the value from col A returned in Col C? DO I have that right? If so, then you just want col A returned all the time. So just copy col A into Col C. Look at your example again.

 

Upvote 0

T

tugal

New Member

Joined

May 4, 2005

Messages

12

• May 4, 2005

• #7

I guess I'm not explaining this the right way. If the value matches in both column a and b then return that value in column C. If it doesn't not match in both columns then return nothing.

 

Upvote 0

steve case

Well-known Member

Joined

Apr 10, 2002

Messages

823

• May 4, 2005

• #8

In Cell "C2" put in the following formula and copy it on down.

=IF(A2=B2,B2,"")


Note: Your example in Cell "C5" shows a "d" when it should probably be blank.

 

Upvote 0

T

tugal

New Member

Joined

May 4, 2005

Messages

12

• May 4, 2005

• #9

No that was purposely done to show my example. Cell "C5" shows a "d" because it exists in Column B just not in the same order as it does in Column A. That's where my problem lies.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• May 4, 2005

• #10

tugal said:

No that was purposely done to show my example. Cell "C5" shows a "d" because it exists in Column B just not in the same order as it does in Column A. That's where my problem lies.

Click to expand...

aaExtractCommonList tugal.xls
	A	B	C	D
1			# Of Common Items	4
2	List1	List2	0	Common List
3	a	a	1	a
4	b	b	2	b
5	c	c	3	c
6	d	w	4	d
7	e	x
8	f	d
9	g	z
10
Sheet1

Formulas...

C2 must house a 0.

C3, copied down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0))*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,C:C,A:A),"")

Note that the foregoing shares the same core as my contrib in the link rs2k quoted.

 

Upvote 0