create list from table

davidaw_100

New Member
Joined
Oct 9, 2002
Messages
5
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
You'd have to provide more details, but I feel certain the the answer is "Yes".
 

davidaw_100

New Member
Joined
Oct 9, 2002
Messages
5
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

Book1
ABCDEFGHIJKLMNOPQRSTU
1Step1:Step2:Step3:
29am10am11am9am10am11am9am10am11am9am10am11am
310/1/200250060070010/1/200250060070010/1/200250060070010/1/20029am500500600700
410/2/200280090095010/2/200280090095010/1/200210/1/200210am600
510/1/200210/1/200210/1/200211am700
610/2/200210/2/200280090095010/2/20029am800800900950
710/1/200210/2/200210/2/200210am900
810/2/200210/2/200210/2/200211am950
Sheet1

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

davidaw_100

New Member
Joined
Oct 9, 2002
Messages
5
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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

davidaw_100

New Member
Joined
Oct 9, 2002
Messages
5
The formula showed up in the cell as the result{=TRANSPOSE($S$2:$U$2)}. I did not get a value.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top