Compare two columns and show sum of matches and sum of non-matches

[hyenajackrabbit]

Hi,


I need to compare column A on the 'Event IDs in ED' worksheet with column A on the 'All_Alerts' worksheet to get the number of matches and the number of mismatches including blank cells on the All_Alerts worksheet.


I need to show the sums of matches and the number of mismatches in separate cells.


I used the formula below to get the matches but SUMPRODUCT takes too long. This is a test workbook. The true data will be over 23000 lines. The column size may vary also so I can't specify a range other than a column.


=SUMPRODUCT(COUNTIF('Event IDs in ED'!A:A,All_Alerts!A:A))


I tried some IF(MATCH statements but I could not get the TRUE or FALSE to summarize to a value.


The file is being shared on Google Docs:

https://docs.google.com/open?id=0B9Mjsc_Lt5hDWU91RHZINVJ5SDA


Thanks ahead of time for your help.

 

[Faseeh]

Hi hyenajackrabbit,


Welcome to the forums!!!


You can reduce the time taken by this formula by not selecting entire columns as i can guess the criteria range must be smaller then the entire column where you are looking into, i suggest you change the formula to:

=SUMPRODUCT(COUNTIF('Event IDs in ED'!A2:A1000,All_Alerts!A2:A23000))

This will speed up the process. The remaining things on summary sheet needs explanation, as i try to understand it further, can you explain it by taking up an example case?


Regards,

Faseeh