X-Y gridded data into columns
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
|