Hi, 

I currently have a list of 80,000 patents (col. A) with their respective backward and forward citations (col. B&C). 

I want to write a formula in column D that identifies a match between any of the citation numbers in column B within the corresponding row in column C. E.g. in row 3, there is a match between BWD and FWD citation (US1012) in column B  and column C. 

My ideal output in column D is the matching value (i.e US1012). If no matches are found between col.B & C, the corresponding cell in column D can remain blank, as shown. 

My main problem (column E) is I want to have a way of identifying if the value in column D (i.e US 1012)  is present in column A (list of original patent IDs). If so, I wanted an automated quick method of  highlighting the corresponding row number in column A containing the matching citation in column D. The intention is to delete these matching patent IDs (i.e delete row 14 US1012). 

Overall example, 

Column D identifies US 1012 is present in both column B and C. 

Column E highlights the row containing US1012 in column A. This methodology would be applied to a spreadsheet with 30,000 rows; I will then delete all matching citation rows  (i.e row 14 with US1012 in col A). 

Any help is appreciated.

Many thanks

​Hi,  I currently have a list of 80,000 patents (col. A) with their respective backward and forward citations (col. B&C).  I want to write a formula in column D that identifies a match between any of the citation numbers in column B within the corresponding row in column C. E.g. in row 3, there is a match between BWD and FWD citation (US1012) in column B  and column C.  My ideal output in column D is the matching value (i.e US1012). If no matches are found between col.B & C, the corresponding cell in column D can remain blank, as shown.  My main problem (column E) is I want to have a way of identifying if the value in column D (i.e US 1012)  is present in column A (list of original patent IDs). If so, I wanted an automated quick method of  highlighting the corresponding row number in column A containing the matching citation in column D. The intention is to delete these matching patent IDs (i.e delete row 14 US1012).  Overall example, Column D identifies US 1012 is present in both column B and C. Column E highlights the row containing US1012 in column A. This methodology would be applied to a spreadsheet with 30,000 rows; I will then delete all matching citation rows  (i.e row 14 with US1012 in col A).  Any help is appreciated. Many thanks   Read More

​