Comparing Column B to Column A

[TaGY2K]

Ok... say I have like 1,000 records on column A and column B ..is there
a way to write a formula say... going down column B and compare to
column A and tell me which number or char. is not in colum A but is in
column B ..or vice versa ..
ie

A B C

12 12
13 14 13
14 15
15 18 18
16 16
17 17


I tried write an if statement .. if true =1 false = 0 ...but that
doesn't work..

 

[Biff]

Hi!

Here's one way: (based on your sample)

This will return the values from column A that do not appear in column B.

Assuming the range of data starts on row 2.

Enter this formula in C2. This is an array formula. Instead of typing the
formula and hitting the ENTER key, you type the formula then use the key
combination of CTRL,SHIFT,ENTER. That is, hold down both the CTRL key and
the SHIFT key then hit ENTER. Also, if you ever edit an array formula it
must be re-entered using the key combo.

=INDEX(A$2:A$7,SMALL(IF(COUNTIF(B$2:B$7,A$2:A$7)=0,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

If you don't want to see the errors use this version (still array entered):

=IF(ROWS($1:1)<=SUMPRODUCT(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SMALL(IF(COUNTIF(B$2:B$7,A$2:A$7)=0,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"")

If you want to switch it around and return the values from column B that do
not appear in column A then all you need to do is "flip" these references:

From: MATCH(A$2:A$7,B$2:B$7,0)
To: MATCH(B$2:B$7,A$2:A$7,0)

From: COUNTIF(B$2:B$7,A$2:A$7)
To: COUNTIF(A$2:A$7,B$2:B$7)

Biff

 

[Max]

Try this previous response to a similar query:
http://tinyurl.com/gywoc

There's a link to a sample file in the response
which demonstrates the construct and it's workings

The sample shows how to compare *numbers* in 2 cols
and auto-extract matched and unmatched numbers in ascending order

If the source data is a mixed bag of items comprising *numbers and
text*,
we could just change the formulas in cols G to J to:

In G1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW()))
In H1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),""))
In I1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))
In J1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),""))

Then select G1:J1, fill down
to cover the max expected extent of data in cols A and B

(No change to the formulas in cols C to F)

Then

Col C will return items in col A found in col B
Col D returns items in col B found in col A

Col E returns items in col A not found in col B
Col F returns items in col B not found in col A

All results in cols C to F will continue to be neatly bunched at the
top
(The resulting items will of course, no longer be in ascending order)

 

[Guest]

ASAP Utilities, which is a free add-in easily found by Googling "ASAP
Utilities," makes this an easy job.

After installing the add-in, highlight the range that includes your lists,
and go to "ASAP Utilities/Information/Count Duplicates in Selection." This
will color the cells that contain duplicate data (regardless of order) and
give you a count of duplicates. Easy as pie.

Incidently, this is exactly the answer I gave to a posting just a few days
ago. You can often find answers to your questions by searching in the forum
-- also easy as pie: Just type a key word like "duplicates" in the search box
above.