create list from table

[davidaw_100]

I have a table with rows as dates and individual measures. The columns are time segments (e.g. 30 min slots). Is there a fast way to make the table into a list that I can use in other programs? I am thinking that the list will contain the 4 columns measure, date, timeslot, value

 

[Mark W.]

You'd have to provide more details, but I feel certain the the answer is "Yes".

 

[davidaw_100]

My Table looks like this:


Measure (e.g. Sales)

Date / Time
9am 10am 11am
10/1/02 500 600 700
10/2/02 800 900 950


Can I get the data to look like this


Date Time Sales$
10/1/02 9 500
10/1/02 10 600
10/1/02 11 700
10/2/02 9 800
10/2/02 10 900
10/2/02 11 950

 

[DonkeyOte]

Run a record macro to have a look at the code - highlight some data in a row, copy it, go somewhere else and do paste special - but use TRANSPOSE...

Better still, record what you want to do and then look at the code...if you still have any problems drop the code into a message so we can all help to amend where necessary but I reckon you'll be able to do this.

So Tools - Macro - Record New Macro - then do what you ask...


Luke :wink:

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1						Step1:					Step2:					Step3:
2		9am	10am	11am			9am	10am	11am			9am	10am	11am					9am	10am	11am
3	10/1/2002	500	600	700		10/1/2002	500	600	700		10/1/2002	500	600	700		10/1/2002	9am	500	500	600	700
4	10/2/2002	800	900	950		10/2/2002	800	900	950		10/1/2002					10/1/2002	10am	600
5						10/1/2002					10/1/2002					10/1/2002	11am	700
6						10/2/2002					10/2/2002	800	900	950		10/2/2002	9am	800	800	900	950
7						10/1/2002					10/2/2002					10/2/2002	10am	900
8						10/2/2002					10/2/2002					10/2/2002	11am	950
Sheet1

This message was edited by Mark W. on 2002-10-10 14:13

 

[davidaw_100]

Thanks. I thought there might be a solution with array formulas. When I copied the formula over to my sheet and adjusted the cells, all I got was text. Is there s.t. I need to do to get the values like you did? This is my first posting on this website, so I am not really familiar w/how the embedded spreadsheet works.

 

[Mark W.]

Precisely, what do you mean by "...all I got was text"?

 

[davidaw_100]

The formula showed up in the cell as the result{=TRANSPOSE($S$2:$U$2)}. I did not get a value.

 

[Mark W.]

Ahh... Don't type the outer-most braces, {}. Excel supplies them when you enter the formula using the Control+Shift+Enter key combination. See the Excel Help topic for "About array formulas and how to enter them".

 

[davidaw_100]

Thanks! Got the array formula to work. I appreciate your help.