How to compare two numeric column in same worksheet

[Guest]

Hi,


Please suggest me how to match Id and Student ID. I have 3000 Student ID and
8000 id is same excel worksheet. How can i get only match Id's in column C.
Any formula please suggest.

id Student ID
195254 195903
195703 196010
195903 198787
196010 205653
196643 208498
196646 209069
196675 209168
197301 210864
197444 210934
198712 221614
200720 370825
202194 451482
203212 451761
210864


Thanks a lot.

 

[Guest]

=IF(A1=B1,A1,"")
enter in C1 and copy down

 

[Guest]

Hi,

I tried the formula but i don't see any results in column C.

 

[Guest]

Source data assumed in cols A and B, from row 2 down

In C2:
=IF(ISNUMBER(MATCH(B2,A:A,0)),ROW(),"")
(Leave C1 blank)

In D2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW(A1))))
Select C22, copy down to the last row of data in col B (ie Student id).
Col D will return the list of matched Student ids, all neatly bunched at the
top.

 

[Guest]

Thanks a lot. This formula works.

Source data assumed in cols A and B, from row 2 down

In C2:
=IF(ISNUMBER(MATCH(B2,A:A,0)),ROW(),"")
(Leave C1 blank)

In D2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW(A1))))
Select C22, copy down to the last row of data in col B (ie Student id).
Col D will return the list of matched Student ids, all neatly bunched at the
top.

 

[Max]

Glad to hear that. You're welcome.