compare 2 columns if the value is equal

[kayza]

hi.
I want to compare the data I have in two different columns, let's say column A and column B, which in Column A contains old data, while column B contains the latest data and has some data that is not in column A.

A	B
John	Robert
Tina	Greg
Greg	Mike
Ken	Ken
Avril	John

<tbody>
</tbody>

and the result I want to show is that the data in column C only contains the same value and is written in ascending order, and more or less will look like the following

C

John

Ken

Greg

<tbody>
</tbody>

Thank you in advance
sorry if this thread has been discussed in the forum

 

[Swapnil Shah]

hi,

Use following array formula in column C. Press Ctrl+Shift+Enter not just enter

=IFERROR(INDEX($B$2:$B$6,LARGE(IFERROR(MATCH($A$2:$A$6,$B$2:$B$6,0),""),ROWS($A$2:A2))),"")

Change range as per your need.

Thanks
Swapnil Shah

 

[kayza]

=IFERROR(INDEX($B$2:$B$6,LARGE(IFERROR(MATCH($A$2:$A$6,$B$2:$B$6,0),""),ROWS($A$2:A2))),"")

Hi Swapnil Shah.
Thanks for your quick response. it works. but the data did not display like I want to, which is it written in Ascending order.

 

[Swapnil Shah]

For given set of data , it gives output as desired , i.e. John,Ken,Gerg

IF you want your entire list to be alphabetically sorted , you can sort the out put column as per your need manually. By means of formula it'll be difficult to achieve.

Thanks
Swapnil Shah