Hello. I have datasets exported from map data as large grids
(1024x768). I want to transform them into 3 long (768000 rows)
columns, with X, Y, and the value from the grid. Example:

B A A
C D A
C C D

to be put into:

X Y value
1 1 B
2 1 A
3 1 A
1 2 C
2 2 D
3 2 A
1 3 C
2 3 C
3 3 D

Can anyone help?

On Sun, 15 Nov 2009 11:22:52 -0800 (PST), CM
wrote:

Hello. I have datasets exported from map data as large grids
(1024x768). I want to transform them into 3 long (768000 rows)
columns, with X, Y, and the value from the grid. Example:

B A A
C D A
C C D

to be put into:

X Y value
1 1 B
2 1 A
3 1 A
1 2 C
2 2 D
3 2 A
1 3 C
2 3 C
3 3 D

Can anyone help?


In cell A2 put the following formula:
=INT((ROW()+x-2)/x) (where x is the number of columns in your grid)

In cell B2 put the following formula:
=MOD(ROW()-2,x)+1 (where x is the number of columns in your grid)

In cell C2 put the following formula:
=INDEX(mygrid,A2,B2) (where mygrid is the range for your grid)

Copy cells A2:C2 down to row x*y+1 (where x and y are the number of
columns and rows respectively in your grid)

Hope this helps / Lars-Åke

On Nov 15, 2:21*pm, Lars-Åke Aspelin
wrote:
On Sun, 15 Nov 2009 11:22:52 -0800 (PST), CM
wrote:





Hello. I have datasets exported from map data as large grids
(1024x768). I want to transform them into 3 long (768000 rows)
columns, with X, Y, and the value from the grid. Example:

B A A
C D A
C C D

to be put into:

X Y value
1 1 B
2 1 A
3 1 A
1 2 C
2 2 D
3 2 A
1 3 C
2 3 C
3 3 D

Can anyone help?

In cell A2 put the following formula:
=INT((ROW()+x-2)/x) * *(where x is the number of columns in your grid)

In cell B2 put the following formula:
=MOD(ROW()-2,x)+1 *(where x is the number of columns in your grid)

In cell C2 put the following formula:
=INDEX(mygrid,A2,B2) *(where mygrid is the range for your grid)

Copy cells A2:C2 down to row x*y+1 * (where x and y are the number of
columns and rows respectively in your grid)

Hope this helps / Lars-Åke

Thank you very much. This works well.
cheers,
CM