how to get common value in two row comparision

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
hi to all,
i have data like this
sx1 sx2 sx3 sx4 sx5
p1 -/- ZZ AA TT ZZ
p2 AA TT AA TT CC
and i am expecting data like this
sx1 sx2 sx3 sx4 sx5
p1 -/- ZZ AA TT ZZ
p2 AA TT AA TT CC
expected AA TT AA TT CC
i mean i want to compare P1 with P2 from SX1 to SX5 and i want to get common data from either P1 or P2 no matter really from which cell (like in my expected row), but i want to get value if any one of P1 or P2 contains -/- or ZZ then i should get value from eihter of P1 or P2 i mean not to get either ZZ or -/-. (like in my expected row).
I need help to solve this problem help would be appreciated
Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top