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

Probably better to add another column simultaneous to your column B..

=IF(AND(LEN(B3)=5,B3=LEFT(B4,5)),B4,B3) copied down.

Does this work?

=IF(LEN(B3)=5,INDEX($B$3:$B$35,MATCH(1,INDEX((LEFT($B$3:$B$35,5)=B3)*(LEN($B$3:$B$35)>5),0),0)),B3)

copied down.

I am not sure I fully understand.

Please elaborate.

Also, if this is a new question, it should be in a new thread.