Repeat Cell Values depending on Value in an Adjacent column

Hello!

I have a large excel file with multiplel marcos etc. that helps me import data from a CSV file from our head office in Japan into our ERP.
Everything works very smoothly, but I have one minor issue that I just cannot wrap my head around...
Attached is a simplified table that will help the task I need to achieve:

Sheet A - DATA - This is the info that I receive from Japan
Sheet B - CURRENT - This is what I currently achieve with a simple formula
Sheet C - NEEDED - This is what I need to achieve > the colour should be repeated in each row that has the same reference (the first 7 characters of column A).

I hope that the sample file helps illustrate what I need to achieve. Either VBA or a Formula would work for me, but I can't figure out the best way to approach this... any help would be much appreciated!

Thanks in advance,

Reno

Try this formulas for C2, D2, E2 and F2

Please Login or Register  to view this content.

See the file

Dear José,

Thank you so much for your reply & help! Definitely going in the right directly - very neat! I looked into the code and adapted/facilitated it slightly which helps. But am still running into a problem if there are 5 colours for example. Please see updated sample file attached with the lines concerned in RED.

Thanks so much for your help!

Remo

Dear JeteMc,

First of all, thanks a million for taking the time to respond and sorry for my slow reply.
I just tested the formula you suggested and it works like a charm. Magic!
Your help is really very much appreciated, thank you so much!

All my best,

Reno