Array Transformation Function Needed

[Petro]

Hi there,
I have the following issue:
I need to transform arrays of data according to the following form:

Source Data:

1 2 4 8 9
1 23 67 78 111 589
7 42 34 78 121 45
8 24 34 78 131 455
9 24 456 43 145 45
10 24 56 34 456 54

This has to be transformed to:

1 2 3 4 5 6 7 8 9 10
1 23 67 0 78 0 0 0 111 589 0
2 0 0 0 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0 0 0
6 0 0 0 0 0 0 0 0 0 0
7 42 34 0 78 0 0 0 121 45 0
8 24 34 0 78 0 0 0 131 455 0
9 24 456 0 43 0 0 0 145 45 0
10 24 56 0 34 0 0 0 456 54 0

The final array is always 64x64 cells large. The source arrays are equal to or less than 64x64. The maximum number of missing rows or columns is ~8. There is NO pattern as to which rows or columns are missing. Each row and column posesses an identifying number (see above). Though this seems to yell for some lookup type of function, I haven't quite figured it out... Oh, and I have to do the same job on a whole bunch of different files - but thats a different question.

Any help on this issue is very welcome!! Thanks a million in advance!!
-Petro

[Andy Pope]

The source array is read from the worksheet, range A1:F6

The transformed array is outputted starting at A10

Please Login or Register  to view this content.

[Petro]

Thanks a million Andy!
This macro runs perfectly! Can I acknowledge you in a scientific publication?
Cheers,
-Petro

edit: If you want the final array to be 64 by 64 cells wide plus contain the index row and column headers make sure you change line 6 of the macro to

Please Login or Register  to view this content.

[Andy Pope]

Your welcome and by all means