Extract data from table to make a list of cell values

  • Thread starter Thread starter JoeBlow
  • Start date Start date
J

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.
 
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
 
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!
 
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
 
Back
Top