matching values in columns that contain duplicates

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!!!!

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.



jellybean wrote:
>
> 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!!!!
>
>
> --
> jellybean
> ------------------------------------------------------------------------
> jellybean's Profile: http://www.excelforum.com/member.php...o&userid=37399
> View this thread: http://www.excelforum.com/showthread...hreadid=570856

--

Dave Peterson

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 wrote:
> 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!!!!
>
>
>
> --
> jellybean
> ------------------------------------------------------------------------
> jellybean's Profile: http://www.excelforum.com/member.php...o&userid=37399
> View this thread: http://www.excelforum.com/showthread...hreadid=570856

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!

I don't see a pattern there.

jellybean wrote:
>
> 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
> ------------------------------------------------------------------------
> jellybean's Profile: http://www.excelforum.com/member.php...o&userid=37399
> View this thread: http://www.excelforum.com/showthread...hreadid=570856

--

Dave Peterson

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?

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:D
edit|copy
edit|paste special|values

Now my formulas are gone.

With C:D 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 wrote:
>
> 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?
>
> --
> jellybean
> ------------------------------------------------------------------------
> jellybean's Profile: http://www.excelforum.com/member.php...o&userid=37399
> View this thread: http://www.excelforum.com/showthread...hreadid=570856

--

Dave Peterson

dave

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

jellybean

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

Glad it worked for you.

jellybean wrote:
>
> dave
>
> thank you so much for your help with this one....much appreciated!!!
>
>
> jellybean
>
> --
> jellybean
> ------------------------------------------------------------------------
> jellybean's Profile: http://www.excelforum.com/member.php...o&userid=37399
> View this thread: http://www.excelforum.com/showthread...hreadid=570856

--

Dave Peterson