Comparing two columns

[kbreen]

HI,
I HAVE TWO COLUMNS POPULATED WITH COMPANY NAMES. ONE COLUMN HAS, LET'S SAY, 700 NAMES AND THE OTHER HAS 1,500. WHAT I NEED TO DO IS COMPARE THE TWO COLUMNS SO I CAN FIND OUT, (PERHAPS IN A NEW THIRD COLUMN) WHICH COMPANIES ARE IN BOTH OF THE TWO COLUMNS. IS THERE A WAY TO DO THIS INSTEAD OF DOING A MANUAL, SIDE-BY-SIDE COPY AND PASTE JOB?
THX,
KB

 

[Juan Pablo González]

Well, since I'm onto SQL this days, I would suggest doing an external query, using the Excel file where you have your lists, and using an SQL command like this:

SELECT `Sheet1$`.Name
FROM `C:Test`.`Sheet1$` `Sheet1$`, `C:Test`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.Name= `Sheet2$`.Name

basically, you're creating an intersection between the two lists and returning the results.

 

[SamS]

On 2002-10-09 16:41, kbreen wrote:
HI,
I HAVE TWO COLUMNS POPULATED WITH COMPANY NAMES. ONE COLUMN HAS, LET'S SAY, 700 NAMES AND THE OTHER HAS 1,500. WHAT I NEED TO DO IS COMPARE THE TWO COLUMNS SO I CAN FIND OUT, (PERHAPS IN A NEW THIRD COLUMN) WHICH COMPANIES ARE IN BOTH OF THE TWO COLUMNS. IS THERE A WAY TO DO THIS INSTEAD OF DOING A MANUAL, SIDE-BY-SIDE COPY AND PASTE JOB?
THX,
KB

kbreen, don't know if this is exactly what you are after but this should return in column C all matches between column A & B

=IF(COUNTIF(A1:$B$1,$B$1500),"",A1)

what this formula does is looks at the reference in col A and searches the list in Col B and returns the reference if it exists - only returns unique records. Copy this formula into all cells in col C. The formula ha been based on the fact that col B contains the longer list