Merge multiple columns into single column based on ID

Hi there!

I have a table similiar to that on the screenshot but with more than 10 000 rows.

How can I combine multiple columns into single column based on ID without losing data?
Attached sreenshot and file speak for themselves.

example.png

Multistep process:

1) Rebuild the table using INDEX functions to grab all five supplier/supplier_code pairs and put on it's own row with ID code etc.
I've attached a worksheet that shows how I did this. Note that you will have to change the references in the CEILING and MOD functions (plus their arithmetic modifiers) if the real data does not match the example data's five supplier/supplier_codes pairs, and how the data starts in row 4.

2) Copy => Paste special (data only) either into a new field or over the top of that lookup table.
Copy can be launched by CTRL+C or from the tooltip (right clicking on the selected cells).
Paste Special can be launched from tooltip, you want the "Data Only" option under the Paste Options.

3) Trim out the unwanted rows.
Filter the table to hide all "Supplier" fields that are zeros. (These are fields that were blank without a supplier in the original data).
Then you can delete those hidden rows by launching Document Inspector.

Those are not the correct changes. If you have five supplier rows across, you need the following instead:

Please Login or Register  to view this content.

This will take the row number of D9, subtract 8 (9-8 =1), divide by five (=0.2) and round up to the nearest integer (=1).

Pull that down and you will see that this repeats each ID code 5 times.

You will have to pull this down to row 50,000 or whatever to get it to repeat your 10,000 rows for five times.

You can dropfill all the formulas after the first one though just by clicking the plus-sign when you hover over the bottom right corner of the cell, which will automatically pull down to the last row that has a value in it to the cell on the left.

Please Login or Register  to view this content.

Again, row A9 -8 =1, divide by 5, then take the modulus (the leftover after dividing); then multiply by two, and add one. This will displace the correct count over to get the right INDEX.