View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Lars-Åke Aspelin[_2_] Lars-Åke Aspelin[_2_] is offline
external usenet poster
 
Posts: 913
Default 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