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!

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[Anasurimbor]

Yes, that works perfectly. Thank you very much.

[WideBoyDixon]

If you're using Windows then you could try something like this:

Please Login or Register  to view this content.

Edit: Ah, too late I see

WBD

[Anasurimbor]

If you're using Windows then you could try something like this:

Please Login or Register  to view this content.

Edit: Ah, too late I see

WBD

Both work. But I still have a problem.

If I have a job order like in the attachments, where the company name is the same but the reciept number is different, it should output the company name two times (because of the two reciept numbers):
200210751 | ANDRÄ Tief- & Hochbau GmbH | Kabelbau Leipzig GmbH | Kabelbau Leipzig GmbH

[WideBoyDixon]

Small change then:

Please Login or Register  to view this content.

WBD

[jindon]

Try change to

Please Login or Register  to view this content.

[Anasurimbor]

Small change then:

Please Login or Register  to view this content.

WBD

Now that makes sense. It works perfectly. Thanks again, you saved my ***.