Check values and Add Missing

Hi,

I have a tough one here. I have a column of values that I'm cleaning up so they are consistent. Sometimes there are values that have had a few characters cut off in the data collection process. I need a formula that will scan the cleaned up column and identify if there are values that have been cut (since there is a matching initial portion of a text string that matches) and then concatenate the missing values.

For example:
In the attached workbook you will notice cell B19 should match B20:B23, but doesn't because the "AV#***" was cut off in the raw data. I need a formula that will dynamically identify this and concatenate a "AV#***" after the "AV321" in cell B19.

In cell B28 you will see another one that needs the "AA#AABA".

Thanks a ton!!
john