Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comparing Columns
Hello, I've got two massive columns of numerical data, A and B. I need to 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 can get this done with a formula. Thank you in advance for your guidance. Robert -- eurotransient ------------------------------------------------------------------------ eurotransient's Profile: http://www.excelforum.com/member.php...o&userid=27794 View this thread: http://www.excelforum.com/showthread...hreadid=473024 |
#2
|
|||
|
|||
eurotransient Wrote: Hello, I've got two massive columns of numerical data, A and B. I need to 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 can get this done with a formula. Thank you in advance for your guidance. Robert 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. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=473024 |
#3
|
|||
|
|||
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 "eurotransient" wrote: Hello, I've got two massive columns of numerical data, A and B. I need to 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 can get this done with a formula. Thank you in advance for your guidance. Robert -- eurotransient ------------------------------------------------------------------------ eurotransient's Profile: http://www.excelforum.com/member.php...o&userid=27794 View this thread: http://www.excelforum.com/showthread...hreadid=473024 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Two Columns of Text | Excel Discussion (Misc queries) | |||
Comparing text in columns | Excel Discussion (Misc queries) | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |