Automated Excel Lookup Over Inconsistent Range (Strange Format)

Question

I am trying to figure out how best to automate a "lookup" process of sorts (really it's more so just an attempt to copy and paste a specific value) over this strange document format I am working with. It looks as follows:

So what I am trying to do is to get the product code from column D (e.g. D2) which is associated with buyers from the rows directly below (e.g. C3 and C4) which is 1,,,x buyers (so the amount of buyers is not a constant) and then fill the value in front of said buyers (column B e.g. B3 and B4) with the respective product code. I haven't used VBA before (although I am fairly familiar with python) so I imagine there could be a solution that is related to that, but I can't seem to find any way to do it without using some form of more "advanced" coding than basic excel formulae inside cells would allow. Wondering if anyone could help me figure our what I should do.

NOTE - in columns E and beyond there will be data in regard to how much of a product that a given buyer has purchased. Not sure if this is relevant, but maybe it gives more insight as to what I am trying to do.

Answer 1

If the pattern is always the same, meaning that there is a blank cell between the buyers of various product codes then you can try the following formula on column B:

=IF(AND(B2="",D2<>""),D2,"")

Drag it down to fill the other cells. The formula checks if the cell above is empty and if the respective cell in column D is not empty. If it is true/yes, then copy the value from column D to cell on the row below in column B.