Extract data from table to make a list of cell values

[JoeBlow]

I have a table with column-headings and row-headings. I would like to
convert this table into a list with three columns.
The the list will extract the row-heading, the column-heading, and the
cell-value. The cell value is a text string.

For example.

room# bed bathroom kitchen

1 king size hot tub microwave only

4 bunk bed toilet only full kitchen

32 single bed broken sink no kitchen

My goal is to generate a list that shows the row-value, the
column-value, and the corresponding cell-value for the ENTIRE table. I
don't suppose it matters the sequence the list is generated (eg. left to
right or top to bottom) because I could just sort it with SORT.

My list would look like this:

room# area cell-value

1 bed king size
1 bathroom hot tub
1 kitchen microwave only
4 bed bunk bed
4 bathroom toilet only
4 kitchen full kitchen
32 bed single bed
32 bathroom broken sink
32 kitchen no kitchen

Is this task possible?

Thanks for any help.

 

[Frank Kabel]

Hi Joe
try the following formulas on your second sheet (assumption row 1 is a
heading row):
A2:
=OFFSET('sheet1'!$A$2,INT((ROW()-2)/3),0)

B2:
=OFFSET('sheet1'!$B$1,0,MOD(ROW()-2,3))

C2:
=OFFSET('sheet1'!$B$2,INT((ROW()-2)/3),MOD(ROW()-2,3))

and copy these formulas down

 

[JoeBlow]

Thanks Frank!

It is working for the first two columns, but C2 displays a "0" instead of
the cell value (text string). I'm not sure why. Also, I was just using
my 4x4 table as an example. I actually have a table 7 columns x 234 rows
(first row and column are headings). How do I modify your fomulas to
complete the list?

The way I understand it, I can place these formulas anywhere to start my
list anywere in a new sheet (i.e, i don't have to start at A1). Correct?

Also, I have copy these formulas down well beyond 234 rows (i.e. my list
will be 233*6=1398 rows).

What will happen if I encounter merged cells within my table? I was
planning on removing the merge, but if i doesn't matter i will leave them.

Thanks again, there is hope!

 

[Frank Kabel]

Hi
if you like email me your file with your specific set-up and I'll
insert the formulas for you. Please don't use merged cells!!! They will
cause serious trouble in most cases
email: frank[dot]kabel[at]freenet[dot]de