Comparing Columns

  • Thread starter Thread starter eurotransient
  • Start date Start date
E

eurotransient

Hello,

I've got two massive columns of numerical data, A and B. I need t
compare the content of the two columns and come up with a column "C"
which consists of all numbers that appear ONLY in column B.

Please help...this will eliminate a full day of tedious work if I ca
get this done with a formula.

Thank you in advance for your guidance.

Rober
 
eurotransient said:
Hello,

I've got two massive columns of numerical data, A and B. I need t
compare the content of the two columns and come up with a column "C"
which consists of all numbers that appear ONLY in column B.

Please help...this will eliminate a full day of tedious work if I ca
get this done with a formula.

Thank you in advance for your guidance.

Robert
Click to expand...

Enter this formula in C1 (adjusting the range to suit your needs)

=IF(COUNTIF($A$1:$A$100,B1)=0,B1,\"\"

and copy down until your range requirements are met.

Regards
 
Finding all the entries in column B that are not in column A

Quick & Powerful if you use the MS Query tool under Data->Import External
Data->New Database Query

To make this work, take your original file and make sure each column of
numbers has a unique one-word text heading in row 1. In this example I used
the very clever names ColA and ColB.

Highlight the portion of the first column that contains the label and
numbers and select Insert->Names->Create.. and check Top Row, if it isn't
already. Nothing else should be checked.

Do the same thing for the second row.

Save the file

Open a new workbook

THIS WORKS IN EXCEL 2002. The dialogs may be a little different in other
versions.

Select Data->Import External Data->New Database Query. Select Excel file,
and navigate to the file you just saved and select it.

The wizard should show you the two named ranges you just created.

Select the one containing the values you want to isolate (column B in this
problem).

Click on the Next button 3 times, then choose "View Data or Edit Query in
Microsoft Query" and click on the Finish button. You are now in the query
designer

Click on the SQL button and paste in the following query - replacing ColA
and ColB with the names you've chosen


SELECT ColB.F1
FROM ColA ColA right join ColB ColB
on colb.f1=cola.f1
WHERE ColA.F1 is null

Click on OK - you'll get a message that the query can't be shown
graphically. Click on OK

The query will run and generate the list of ColB numbers that are NOT IN ColA

To get them into your new sheet, click on the button that looks like a book
and a left pointing arrow
 
Back
Top