Need a macro to cross reference several columns and grups of rows

Hello,

I am stuck with a problem I have with a spreadsheet, attached is some example data.

In column E there are cells that have entries like this "2965:3372:3117:3168:3314:2936" what I need to do is check each code separated by ":" by looking it up in column A, then checking in column D of that row, If that cel in column D for that row is empty then it is correct though if that cell in column D for that row contains "yes" or "no" then I need it to be able to check column B for the product group (with the same product titles) then return the reference found in column A for the row of that group that has nothing in column D and swap the code in the original list (2965:3372:3117:3168:3314:2936) with this new product code ID.

I hope this makes sense as I am desperate to be able to do this. I cant think of any way to do it with my basic formula and function knowledge.

As an example the cell E3 in the attached example data reads: "1965:1970:2933" this would change to "1962:1968:2929".

I'm very grateful for any help and hope to hear from someone soon.

Thanks,

Paul

example-data-variant-issue.xlsx

See attached file where I used this macro:

Please Login or Register  to view this content.

Regards,
Antonio

Antoka05, that works perfectly thank you very much for that I don't know what I would have done without this awesome macro you have produced here.

Absolute life saver, thank you very much.

Hello Antoka05,

I'm just trying to apply this macro to a bigger spreadsheet and am getting an error message.

Can I ask is there a limitation on the number of rows that this will work for? Also if I have columns running from a to ay does that cause a problem?

From the example data that I sent you columns a, b, c, d & e move to columns c, d, al, aq & ar respectively in the spreadsheet i'm trying to apply the macro to and I have updated the macro with these changes where a reference to these column headers were they appeared. So the macro now looks like this:

Formula:

Please Login or Register  to view this content.

As I say I'm still getting an error message would you be able to help?

Also I have about 9500 rows though have reduced this to 1000 just in case this was causing the error and am still getting the error. Any help with this would be gratefully received.

Thanks and best regards,

Paul