Table Data to Matrix

Hello,

I have a data in table format. The data is not normalized meaning for some rows in some columns the data repeats. I want to transform the data to a matrix with unique row and column captions based on 2 columns of the original data, and then fill the matrix based on 3rd column. I prepared the matrix row and column captions with unique data, but when it comes filling the matrix based on a column value it takes very long time to complete (I have 60,000 rows in the data which i am trying to transform). I would be very grateful if someone helps me to find the fastest way to place the original data into the matrix.

To make it more clear, I attach the small spreadsheet as a sample. The original data is in range "A2:C11" in Sheet1. Based on column "A" I already generated matrix row captions using unique data in column DEAL_ID, and based on unique data on column DEAL_DATE - column captions in Sheet2. The original data is already sorted first according to DEAL_ID column, then DEAL_DATE column. I just need the fastest way to will DEAL_AMOUNT column into the matrix using VBA. I know that I can use INDEX(MATCH()) to do it with built-in functions, but this is only a small piece of total picture, and I need this part in VBA, and INDEX(MATCH()) is slow enough, I think there is a faster way.


Many thanks in advance for your response.

Hi,

Is this fast enough for you ?

Please Login or Register  to view this content.

Regards

I figured that the fastest way is just

Please Login or Register  to view this content.

Really ?

Please upload your workbook, we will make a little comparison benchmark (using Excel as the time counter, not human feeling).

Regards

Ok, in that case, I will provide the data

Please Login or Register  to view this content.

Just run the Sub Benchmark() and watch the result
On my machine :
Mine : 0.7351 seconds
Yours : 11.1822 seconds

Mine take 6.57% time of yours.



Regards