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.
[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Robert[/TD]
[/TR]
[TR]
[TD]Tina[/TD]
[TD]Greg[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[TD]Ken[/TD]
[/TR]
[TR]
[TD]Avril[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 200"]
<tbody>[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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