Transpose data from one column based on criteria of a different column with VBA Arrays

[Anasurimbor]

Hello,

at first I have to say english isn't my first language. But I hope you will understand my problem.

I have attached an example file.

In this I want to tranpose some data from table "Contracts" to table "Overview" for easier filterability. Sadly a PivotTable solution was not viable, since it can't be really filtered the way I need to.

Essentially we want to see which job order has multiple different reciept numbers. And transpose the company names behind the reciept to the table Overview. Max 10 company names. I did this manually in the example Overview.

Not every job order has reciepts. What I didn't show in those table are that every job order has service description. This is for a cable company. So these reciepts are for services like digging a hole to access cables. But not every service has something billable - thats why there are empty cells.

I thought about how to do this in VBA. The only solution I found is maybe load job order rows with the same job order in an array and with a for loop add reciept numbers that are not already contained in the array. And then simply outpout the company name belonging to the reciept per column in the table Overview.

But I don't really know how to start or if there are better options for this kind of problem. Like I don't know how to load job order rows with the same job order number into an array.

Some additional info:

My original data contains over 200.000 rows.

I am using excel 2016.

This is my first time working with arrays, normally I can solve my problems with some for loops and if statements.

I hope someone can help me jump start a solution. Thanks!