Hi, 

I have a specific multi-step task I would like to automate in excel, ideally with the use of a formula. 

I have four columns (A. application year, B. patent ID, C. backward citations and D. forward citations). Columns C and D contain strings (comma separated values). 

First step:

1) For each row, I want to identify if there is a matching string value present in both column C and D. In column E (new column), I want to output the matching citation. E.g. in image file attached, cell E2 contains US1111, the string value that is present in both C2 and D2. 

Second step (column F)

2) I want to then search for patent US1111 (value in E2) in column B (list of all patents) and identify it’s application year (column A). 

Third step (column G)

3) If the output in F2 (matching citation year) is less than the value in A2, then the formula searches column B for value in E2 (US1111) and in the corresponding row, the output is “DELETE”.

An example in the data attached is

Row 2: In C2 and D2, the matching string/citation is US1111. Ideal Output in E2 is “US1111”.

Then formula in F2, searches for US1111 in column B and output’s it’s application year from column A (A16: 1994). Output in F2 = 1994.

Then in G2, new formula compares F2 (1994) to A2 (2024) and if F2<A2 (1994<2024), the formula searches for US1111 (E2) in column B and outputs “DELETE” in the corresponding row column G (G16 in our example spreadsheet as US1111 is located in B16. 

any help would be greatly appreciated!

Many many thanks

​Hi,  I have a specific multi-step task I would like to automate in excel, ideally with the use of a formula.  I have four columns (A. application year, B. patent ID, C. backward citations and D. forward citations). Columns C and D contain strings (comma separated values).  First step:1) For each row, I want to identify if there is a matching string value present in both column C and D. In column E (new column), I want to output the matching citation. E.g. in image file attached, cell E2 contains US1111, the string value that is present in both C2 and D2.  Second step (column F)2) I want to then search for patent US1111 (value in E2) in column B (list of all patents) and identify it’s application year (column A).  Third step (column G)3) If the output in F2 (matching citation year) is less than the value in A2, then the formula searches column B for value in E2 (US1111) and in the corresponding row, the output is “DELETE”. An example in the data attached isRow 2: In C2 and D2, the matching string/citation is US1111. Ideal Output in E2 is “US1111”.Then formula in F2, searches for US1111 in column B and output’s it’s application year from column A (A16: 1994). Output in F2 = 1994.Then in G2, new formula compares F2 (1994) to A2 (2024) and if F2<A2 (1994<2024), the formula searches for US1111 (E2) in column B and outputs “DELETE” in the corresponding row column G (G16 in our example spreadsheet as US1111 is located in B16.  any help would be greatly appreciated! Many many thanks  Read More

​