matrix to normalised table?

[Guest]

i posted this previously but got lost in between other questions so here it
is again. i need to convert matrix looking spreadsheet to a normalised table
so i can feed into Access.

current matrix:

A B C
1 ch1 ch2
2 rh1 y n
3 rh2 n y

required table:

A B C
1 rh1 ch1 y
2 rh1 ch2 n
3 rh2 ch1 n
4 rh2 ch2 y


rh=row headings
ch=column headings

table above is just a sample as i got over 20 columns and 15 rows. a
solution would be to link them manually but it is likely that the number of
columns/rows will change (makes the linking solution very impractical.)

any help would be appreciated.

thank you

 

[Guest]

aeg,

I'm sure there must be a cleaner way to do this, but here is *a* solution.

I've given your current matrix a name, "table", and added a couple of helper
columns for clarity (although these can easily be incorporated into the other
formluae and disposed of). Enter the following formulae into the first row of
columns A-E and copy down to a limit larger than you'll need. Once the length
of the resulting table goes beyond the required data size, it just returns
blanks.

In column A (starting at A1) I've put the row you'll be looking for, using
the formula:

=IF(INT(ROW()-ROW($A$1))>=(COLUMNS(table)-1)*(ROWS(table)-1),"",INT((ROW()-ROW($A$1))/(COLUMNS(table)-1))+1)

In column B I've put the column you'll be looking for, using the formula:

=IF(INT(ROW()-ROW($A$1))>=(COLUMNS(table)-1)*(ROWS(table)-1),"",ROW()-ROW($A$1)+1-(COLUMNS(table)-1)*INT((ROW()-ROW($A$1))/(COLUMNS(table)-1)))

Column C has the first column of the resulting table you want:

=IF(A1="","",OFFSET(table,A1,0,1,1))

Column D has the second column of the resulting table you want:

=IF(B1="","",OFFSET(table,0,B1,1,1))

Column E has the third column of the resulting table you want:

=IF(A1="","",OFFSET(table,A1,B1,1,1))

HTH,
Ryan

 

[Guest]

thanks for that.
although i dont yet understand the functions, i tested it and it works fine.
you saved me lots of time. (but i still feel there would be a code to run
through the 2dim array to generate the output table in excel)

thanks again.