Comparing two lists for uniques

P

P.Rumpz

Hello peoples,

I want to compare two columns of data (A & B) and create a third colum
(C) of the items not appearing on -both- A & B.

Basically, I want to take
=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")
and combine it with
=IF(COUNTIF($B$1:$B$10,A1)=0,A1,"")

Can this be done (maybe with an IF/OR)?



Thank you for any help you can provide,

Pa
 
P

P.Rumpz

Hello again,

What I am trying to extract are the 'non-duplicate' items from bot
lists at the same time. Think of it as a COUNTIF function that point
in both directions at once. Can this be done?


Help!

Thanks!

Pa
 
A

Anders S

Hi Pat,

Here's something to start with

=IF(COUNTIF($B$1:$B$10,A1)=0,A1,"")& " "&IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")

HTH
Anders Silven
 
P

P.Rumpz

Hi Anders,

I guess I should have mentioned that I am using Excel 2000 (I don'
know if it matters in this instance). Excel gave me an error on th
'3DIF' function. I modified the formula you provided into an 'IF &IF
function and it appears to be correctly extracting the 'non-duplicate
items from both lists.

Unfortunately, the output list has a glitch. Some of the cells have tw
data items in them rather than just one. Can you provide som
assistance? Here is my general formula:

=IF(COUNTIF($B$1:$B$100,A1)=0,A1,""
&IF(COUNTIF($A$1:$A$100,B1)=0,B1,"")


Thanks much,

Pa
 
A

Anders S

Hi Pat,

The formula should work OK in Excel 2000.

Don't understand what you mean by the '3DIF' function. Anyhow your current formula works as I intended, with the difference that my formula puts a space between the items when there are two hits on the same line.

What you call a 'glitch' is intentional or unavoidable due to the possibility of two unique items in the same row. What do you want to happen in that case?

Regards
Anders Silven
 
D

Dave Peterson

I'd do this:

I'd use a new worksheet (but you don't need to):

Combine the data into one column.
Copy A's data into A1 of the new sheet
copy B's data after the last used row in A of that new sheet.

Then add a header to A1 (if there isn't one there already).

Then select A1:Axxxx and do
Data|filter|advanced filter
but filter only unique entries and put the list in B1

Debra Dalgleish has nice pictures at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Now you have one single list that contains exactly one value from each list.

Then delete column A (we're done with it).

In b1, put "In List A"
B2:Bxxxx
=isnumber(match(a2,sheet1!a:a,0))
and drag down

in C1, put "In List B"
C2:Cxxxx
=isnumber(match(a2,sheet1!b:b,0))
and drag down.

You could add a formula in D:

=b2&"--"&c2

Apply data|filter|autofilter to columns A:D.

Filter on D
truetrue means both
falsetrue means B, not A
truefalse means A, not B
FalseFalse means not A, not B--hey, this shouldn't happen!

Filter on does not equal truetrue. You could even copy these visible cells to a
new sheet. (Or back to the original!)
 
P

P.Rumpz

Hi Anders,

Never mind the "3DIF" junk My browser displayed your previous post wit
some extra characters inserted....oops.

In the case of two unique items in the same row, is it possible to hav
the two items sent to two rows rather than both on one row with a spac
between them?

Thanks again Anders.

Pat
 
P

P.Rumpz

Hi Dave,

Thanks for the input. That would work quite well for the output I a
trying to generate. However....
This project will be handed over to another dept. in the next week o
so. The users are relatively new to Excel, so I am trying to automat
the spreadsheets as much as possible.

I will keep your suggestion for future reference though. Thanks again


Pat
 
D

Dave Peterson

Maybe you could give them a macro that does this kind of thing:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim destCell As Range
Dim LastRow As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

With curWks
.Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Copy _
Destination:=newWks.Range("a1")
With newWks
Set destCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
End With
.Range("b2", .Cells(.Rows.Count, "B").End(xlUp)).Copy _
Destination:=destCell
End With

With newWks
.Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("B1"), unique:=True

.Columns(1).Delete

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("b1").Resize(1, 3).Value _
= Array("OnA", "OnB", "Status")

.Range("b2:b" & LastRow).Formula _
= "=isnumber(match(a2," _
& curWks.Range("a:a").Address(external:=True) & ",0))"

.Range("c2:c" & LastRow).Formula _
= "=isnumber(match(a2," _
& curWks.Range("b:b").Address(external:=True) & ",0))"

.Range("d2:d" & LastRow).Formula _
= "=b2&""--""&c2"

.Range("a:d").AutoFilter field:=4, Criteria1:="<>TRUE--TRUE"
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Make sure your data is in A:B with headers only in row 1.
 
P

P.Rumpz

Hi Dave

I am going to try the macro solution over the weekend. I am relativel
new the macros so I apprecite the link you provided too.

Thanks for the input.

Pa
 
P

PQHANH

in the C column add this:

=IF(ISNUMBER(MATCH(A2,B:B,0)*MATCH(B2,A:A,0)),"",IF(ISNUMBER(MATCH(A2,B:B,0)),B2,A2))

it will work for you.

slac
 
Top