Comparing 2 arrays
 

Do you know what formula I can use to sort similar items in two arrays
(columns)?
I have a worksheet with 2 columns of numbers. Some of them match, but
some don-t. Is there a way to delete the matching numbers, so I can
only see ones, which don't match?
I would do it by hand, but its hell lot of work :)
Thanks a lot
Nikita

Comparing 2 arrays
 

to make it more clear i can show an example of the worksheet.
54 43
43 12
23 45
56 51
21 13
49 90
122 92
134 2
How can i make excel delete number 43 from 1st and 2nd columns?

Comparing 2 arrays
 

Maybe this will help...

Select Column A.
With A1 the activecell
Format|conditional formatting
formula is:
=countif(b:b,a1)0
make the font color match the fill color (white on white)

Then select column B
and with B1 the activecell
format|conditional formatting
=countif(a:a,b1)0
and apply that same format (white on white)

You'll only see the unique values.

=======================

If you really want to delete those values, then I'd move column B to a new
worksheet.

Then in the first worksheet
in B1
=countif(sheet2!a:a,a1)0
and drag down

In the second sheet:
in B1:
=countif(sheet1!a:a,a1)0
and drag down

Now in each sheet:
select column B
edit|copy
edit|paste special|values
(don't forget the other sheet!)

Now apply Data|filter|autofilter to column B.
Show the True's and delete those visible rows.
Then delete column B
(and same thing for the other worksheet!)

copy the column back to the other sheet if you want. And delete that helper
sheet.

wrote:

to make it more clear i can show an example of the worksheet.
54 43
43 12
23 45
56 51
21 13
49 90
122 92
134 2
How can i make excel delete number 43 from 1st and 2nd columns?

--

Dave Peterson

Comparing 2 arrays
 

thanks a lot Dave. Your solution worked just the way i needed it to.
Thank you and Happy new year