transpose selected data

[bhmcpfs]

I have an imported .txt file with ~10,000 rows of data. In column B, there are group headings (e.g. red, blue, black) Under each group heading is a variable number of rows containing data. I would like to get the group headings at the end of each row (in column Z). Can I do this? I've tried several variations of an IF worksheet function, with little success. I can get the first row beneath the heading correct, but since my reference varies, I'm not knowledgable enough to get further. Any assistance would be helpful. Sorry for the long post.

 

[bhmcpfs]

Beautiful! Thank you, thank you, thank you.