Help: Massive table with same reference columns to be put in the same row!

Hello all

I have a big table with several items with the condition below:

Table.jpg

I need to transpose all this columns (Batch, Expiry date and Qty) in the same row as the reference Code - meaning that I will add columns as Batch 1, Batch 2, Batch 3 and so on... I have more or less 15k items like this and simple copy/paste/transpose is not going to work.

Thanks in advance!
Best regards

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hi bramoz,

In Power Query there is a Fill Down tool that may be what you need when you say "transpose". There is also a UnPivot Tool in Power Query that may also be what you need saying "transpose". Could you show us an example of the result you are expecting, so we can better understand the question?

Hi bramoz,

I took your table and pulled it into Power Query. I did a Fill Down on the first 3 columns and gave it back to sheet2. I think this is a much better format to have your data in. I tried to do a pivot table from there on Sheet2. See if this is what you need.

PQ Fill Down then Pivot.xlsx