Hello,

I would use a Pivot Table for such and also have the data in an Excel table so that the Pivot Table is updated dynamically when new data is inserted.

Hello Anders,

Thanks for your reply.

Unfortunately, this does not help me, the point is to have data per product visualised side by side and not in one column (I am not the end user of this report).

Hello Maja,

It is of course doable using formulas to get this view you want, but it is not recommended as it requires manual work to extend or update the data.
Using VBA would be a better option, but as I am not so good in writing code I leave that section for others.

In attached file you have a simple approach. For this to work (as I have built it) you need to add a helper column, that is column A in the file.
What this helper column does is to give me a unique name for each material (based on material description). I then use this unique name for a VLOOKUP formula in M24 to get get each batch number as I need the unique batch number for the next VLOOKUP formulas in cells N24 and O24. But as you see, each formula is unique and this is of course repeated for the other columns remaining.

If you would get a fifth batch then you need to extend the formulas to cover this change. So if you don't want to use a Pivot Table then I suggest you go for a VBA solution to get a dynamic report layout build. Any way, if it is not so much data and it is more or less static, then this should work fine.

Thanks Anders!

I think this is exactly what I was searching for, very helpful, indeed!

I am also aware this is maybe not the best approach as it requires manual work.

THANK YOU!!!