Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 arrays
thanks a lot Dave. Your solution worked just the way i needed it to.
Thank you and Happy new year |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CORREL with arrays containing null values | Excel Discussion (Misc queries) | |||
vba adding arrays | Excel Discussion (Misc queries) | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
Comparing Arrays | Excel Discussion (Misc queries) | |||
Comparing Arrays | Excel Worksheet Functions |