Extending a table

[Mengo85]

Hi all,

attached you will find an excel file which should help to understand the problem I'd like to solve. In this file there are 2 tables. One is the input file and the other is the desired result/output.

The input table consists of part numbers (Column G) the number of boxes necessary (Column J), the number of orders (Column K) and the volumen of the boxes (Column N). All other columns consist of important data but not relevant for this procedure.

What I'd like to do is to expand the input table as follows. E.g. there are 4 orders for part number 70653 with a total number of 2000 boxes and a total volume of 4.304 m³. I'd like to list every order in a separate row as seen in the Desired RESULT table. In other words 4 Rows each consiting of the part number 70653, 500 boxes, 1 order and 1.076 m³.

In the file I coloured the individual rows to help you visualise the input values and their corresponding output values. As you can see duplication can happen and need to be included. All other empty cells have important information for each row and need to be duplicated as many times as ther are orders.

In the actual file I will use the input table is about 4500 rows big and is on one tab and the results should be posted in another tab.

The whole thing needs to be dynamic and I prefer not to use MarcosSample 6a.xlsx but if it is not inevitable the I am happy with Marcos.

Thanks in advance for your help.

I am looking forward to hearing from you.

[watersev]

hi Mengo85, VB option basing on the sample file provided, press "Run" button on sheet "Input"

[Mengo85]

Hi watersev,

thank you so much. Everytime I press Run the output table is generated in a new TAB. Do you know how to program the Macro so that the output over writes the output table in the same tab?

Thanks in advance.

[watersev]

result would always go to the same sheet named "Output" deleting any data on it if that is what you mean. I overlooked correct column 11 value. It is corrected now.

[Mengo85]

Hi watersev,

thank you so much. It works