matching values in columns that contain duplicates

[jellybean]

i have 2 columns where i need to extract matched/unmatched values
from....

i've tried a few different methods but none seem to be working the way
i want it to because of the duplicate values (the problem is that these
are "legitimate" duplicates)....which is making me question whether it's
even possible?!?!?!

example - if value in column A exists in column B, then remove from
both columns

A B
1 0
1 1
1 2
2 2
3 3
3 3

desired result:

A B
1 0
1 2


i've tried an excel add-on called DigDB....however, it will give me
"matched" results regardless of how many there are (i.e. it will tell
me all the values = 1 and values = 2 are matched eventhough there are
more in one column than the other)...

i tried vlookup but again, the dups create a problem for me...(unless
someone can provide me with a multiple vlookup statement for
this?)....

pls help...i don't want to resort to doing this in multiple layers!!!!

 

[Dave Peterson]

Maybe you can use a formula:

Say in C2 (headers in row 1??)
=if(a2=b2,"same","different")

Then drag down the column

Then select column C and apply
data|filter|autofilter

You can choose to view only the Different's--or choose to show the Same's. Then
you can delete those visible rows and then remove the filter (data|Filter|show
all) to see the remaining.

 

[chris.cudmore]

c1: if(a1=b1,a1,"")
d1: if(a1=b1,b1,"")

copy down.

This will leave blanks where the dupes are, but there's lots of code
posted here to help you eliminate the blanks.

 

[jellybean]

thanks for your quick responses!

unfortunately, i just realized that the example i gave doesn't reflect
the scope of my issue.....please refer to this modified example:

A B
1 0
1 1
1 2
2 2
2 2
3 2
4 2
5 3
5 4
6 4
6 5

desired result:

A B
1 0
1 2
5 2
6 2
6 4

my apologies for overlooking this...pls let me know if this example is
not clear....thx again!

 

[Dave Peterson]

I don't see a pattern there.

thanks for your quick responses!

unfortunately, i just realized that the example i gave doesn't reflect
the scope of my issue.....please refer to this modified example:

A B
1 0
1 1
1 2
2 2
2 2
3 2
4 2
5 3
5 4
6 4
6 5

desired result:

A B
1 0
1 2
5 2
6 2
6 4

my apologies for overlooking this...pls let me know if this example is
not clear....thx again!

 

[jellybean]

hmmmm....let me try that again.....

A B
1 0
1 1
1 2
2 2
2 2
3 2
4 2
5 3
5 4
6 4
6 5

in A the value of "1" is listed 3 times, whereas in B it is listed
once....so i want to remove only 1 of those values from A and the one
in B....

for the value of "2" i want to remove both from A and only 2 from
B.....etc....the "0" stays in B because it doesn't appear in A....:

A B
1 0
1 2
5 2
6 2
6 4

does this explain it a little better?

 

[Dave Peterson]

This seemed to work ok for me.

I put the test data in A1:Axxx and B1:Byyy

I put this in C1 and copied down:
=IF(A1="",NA(),IF(COUNTIF($A$1:$A1,A1)<=COUNTIF(B:B,A1),NA(),A1))

and this in D1 and copied down:
=IF(B1="",NA(),IF(COUNTIF($B$1:$B1,B1)<=COUNTIF(A:A,B1),NA(),B1))

Now I'm left something that looks like this:
#N/A 0
1 #N/A
1 #N/A
#N/A #N/A
#N/A 2
#N/A 2
#N/A 2
#N/A #N/A
5 #N/A
6 4
6 #N/A

The numbers match your numbers--but those #n/a's are ugly.

I selected C
edit|copy
edit|paste special|values

Now my formulas are gone.

With C still selected
edit|goto|special|constants|Errors (only errors, uncheck all the other options!)

Notice that only the errors are still selected.
Edit|Delete|Shift cells up.

I was left with:
1 0
1 2
5 2
6 2
6 4


And that's very close to what you got!

 

[jellybean]

dave

thank you so much for your help with this one....much appreciated!!!


jellybean

 

[Dave Peterson]

Sometimes, things work. Somethings, things don't.

Glad it worked for you.