Listing data from an array using column and row headers

[hydrophil]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi there,

Thanks in advance for your help.

I have an array of data. Let's just say, 4 columns by 4 rows, i.e. 16 cells in a grid and let's say the data is 1 in the first cell to 16 in the last cell. At the start of each row and column I have a name for each column, let's say, A, B, C and D and i, ii, iii, iv.

I want to interrogate that array and list all of the data in three columns below. I.e. To create an x,y,z file for use in another application.

So, the lines would say, sequentially:

A i 1
B i 2
C i 3

and so on down to:

D iv 16

This effectively would give me 16 rows of data in three columns with values in each column being 1: the x axis, 2: the y axis and 3: the (z) value in that cell at that co-ordinate.

I want to make this as simple as possible as I have a huge array to define into these three columns giving me over 200,000 cells of data in total and I need to repeat the process for a number of arrays/grids.

Once again, thank you in advance for hopefully saving me weeks of painstaking cutting and pasting with a few simple formulae.

 

[John_McGhie_[MVP]]

No, Sorry, I don't understand...

Is this what your data looks like?


i ii iii iV
A 1 2 3 4
B 5 6 7 8
C 9 10 11 12
D 13 14 15 16

But I really don't understand where you want to go from there. I suspect
you want the OFFSET function.

Have a look in the Excel Help for "Lookup and reference functions".

The trick is to write a formula that computes the offset of each cell, then
fill down to copy the formula down for 200,000 rows

Hope this helps

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Hi there,

Thanks in advance for your help.

I have an array of data. Let's just say, 4 columns by 4 rows, i.e. 16 cells
in a grid and let's say the data is 1 in the first cell to 16 in the last
cell. At the start of each row and column I have a name for each column,
let's say, A, B, C and D and i, ii, iii, iv.

I want to interrogate that array and list all of the data in three columns
below. I.e. To create an x,y,z file for use in another application.

So, the lines would say, sequentially:

A i 1
B i 2
C i 3

and so on down to:

D iv 16

This effectively would give me 16 rows of data in three columns with values in
each column being 1: the x axis, 2: the y axis and 3: the (z) value in that
cell at that co-ordinate.

I want to make this as simple as possible as I have a huge array to define
into these three columns giving me over 200,000 cells of data in total and I
need to repeat the process for a number of arrays/grids.

Once again, thank you in advance for hopefully saving me weeks of painstaking
cutting and pasting with a few simple formulae.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]