Objective: You are trying to reconcile the descriptions between two sources shown in the tables below (descriptions are associated by its CUSIP and Account Number) by performing a wildcard search for keywords. These are csv files that have been downloaded and imported into Excel. Case is insensitive.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]CUSIP
[/TD]
[TD]Account Number
[/TD]
[/TR]
[TR]
[TD]FHLMC CALLABLE 8/26/17 @100 1.26% 08/26/2019
[/TD]
[TD]ABC123
[/TD]
[TD]123456
[/TD]
[/TR]
[TR]
[TD]FHLMC CALLABLE 8/26/17 @100 1.26% 08/26/2019
[/TD]
[TD]ABC123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]FNMA 1.7% 11/25/2020
[/TD]
[TD]XYZ123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]GNMA CALLABLE 4/10/17 @ 100 1.20% 10/10/2020
[/TD]
[TD]DEF456
[/TD]
[TD]345678
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]CUSIP
[/TD]
[TD]Account Number
[/TD]
[/TR]
[TR]
[TD]FREDDIE MAC 8/26/17 @ 1.26%
[/TD]
[TD]ABC123
[/TD]
[TD]123456
[/TD]
[/TR]
[TR]
[TD]FANNIE MAE 11/25/2020 @ 1.7%
[/TD]
[TD]XYZ123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]GINNIE 10/10/2020 @ 1.20%
[/TD]
[TD]DEF456
[/TD]
[TD]345678
[/TD]
[/TR]
[TR]
[TD]FREDDIE 8/26/17 @ 1.26%
[/TD]
[TD]ABC123
[/TD]
[TD]234567
[/TD]
[/TR]
</tbody>[/TABLE]
User creates and maintains this table below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keywords
[/TD]
[TD]Return
[/TD]
[/TR]
[TR]
[TD]Freddie, FHLMC,Federal Home Loan Mortgage
[/TD]
[TD]FHLMC
[/TD]
[/TR]
[TR]
[TD]Fannie,FNMA, Federal national mortgage
[/TD]
[TD]FNMA
[/TD]
[/TR]
[TR]
[TD]Ginnie, GNMA, government national mortgage
[/TD]
[TD]GNMA
[/TD]
[/TR]
</tbody>[/TABLE]
Commas are the delimiters (there may be spaces or no spaces after the comma).
Starting with the first row going down in table 1, Excel should return "FHLMC", "FHLMC", "FNMA", "GNMA" respectively.
Excel should also do the same with table 2.
It should then check if both are equal and return True/False.
Is this something that can be done using formulas only without resorting to VBA? It's a bit long-winded.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]CUSIP
[/TD]
[TD]Account Number
[/TD]
[/TR]
[TR]
[TD]FHLMC CALLABLE 8/26/17 @100 1.26% 08/26/2019
[/TD]
[TD]ABC123
[/TD]
[TD]123456
[/TD]
[/TR]
[TR]
[TD]FHLMC CALLABLE 8/26/17 @100 1.26% 08/26/2019
[/TD]
[TD]ABC123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]FNMA 1.7% 11/25/2020
[/TD]
[TD]XYZ123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]GNMA CALLABLE 4/10/17 @ 100 1.20% 10/10/2020
[/TD]
[TD]DEF456
[/TD]
[TD]345678
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]CUSIP
[/TD]
[TD]Account Number
[/TD]
[/TR]
[TR]
[TD]FREDDIE MAC 8/26/17 @ 1.26%
[/TD]
[TD]ABC123
[/TD]
[TD]123456
[/TD]
[/TR]
[TR]
[TD]FANNIE MAE 11/25/2020 @ 1.7%
[/TD]
[TD]XYZ123
[/TD]
[TD]234567
[/TD]
[/TR]
[TR]
[TD]GINNIE 10/10/2020 @ 1.20%
[/TD]
[TD]DEF456
[/TD]
[TD]345678
[/TD]
[/TR]
[TR]
[TD]FREDDIE 8/26/17 @ 1.26%
[/TD]
[TD]ABC123
[/TD]
[TD]234567
[/TD]
[/TR]
</tbody>[/TABLE]
User creates and maintains this table below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Keywords
[/TD]
[TD]Return
[/TD]
[/TR]
[TR]
[TD]Freddie, FHLMC,Federal Home Loan Mortgage
[/TD]
[TD]FHLMC
[/TD]
[/TR]
[TR]
[TD]Fannie,FNMA, Federal national mortgage
[/TD]
[TD]FNMA
[/TD]
[/TR]
[TR]
[TD]Ginnie, GNMA, government national mortgage
[/TD]
[TD]GNMA
[/TD]
[/TR]
</tbody>[/TABLE]
Commas are the delimiters (there may be spaces or no spaces after the comma).
Starting with the first row going down in table 1, Excel should return "FHLMC", "FHLMC", "FNMA", "GNMA" respectively.
Excel should also do the same with table 2.
It should then check if both are equal and return True/False.
Is this something that can be done using formulas only without resorting to VBA? It's a bit long-winded.
