#1
|
|||
|
|||
Using functions to restructure data in spreadsheet from rows to cols
Hi all,
I have some temperature data in a spreadsheet. It's organized like this: Col A Col B Col C Date Time Temp Day X 1 am XX Day X 2 am XX Day Y 1 am XX I realized that this arrangement made it difficult to create a graph of the data for analysis. The graph would treat each row of data as one point on the horizontal axis (the temp is on the vertical axis). What I wanted instead was the horizontal axis to be time (e.g. 2 am, 3 am) ignoring the date, and for the points in the graph to be labeled with the date. So I thought that a better arrangement of the temp data would be: Col A Col B (12 am) Col B (2 am) .... Col Y (11 pm) Date Temp Temp ... Temp In this way, I can analyze the data meaningfully with 2 graphs 1) A graph showing the temperatures over the course of a particular date. 2) A graph showing the temperatures at a particular time (e.g. 1 am) on different dates. My question is, how can I populate the data automatically in the original sheet into another sheet with the new arrangement? I tried using the LOOKUP function (LOOKUP(Q1, Sheet1!B:B,Sheet1!C:C)), but I ran into a wall because I only want Excel to look into the cells of a row which match the date. So for instance, in the row of the new sheet for Day X, under 1 AM, I would want Excel to lookup the time column in the old sheet for 1 AM, then populate the cell with the temp data on the cell next to 1 AM in the old sheet. Same goes for 2 am, 3 am... But I do not know how to limit this search to just cells in the old sheet matching Day X in the Date column. Thanks for reading. Hopefully this is something which can be simply solved. Of course, if there is a better way to go about doing this, I am all ears. Cheers, Jay |
#2
|
|||
|
|||
It would be easier to look at your file as it is rather than try to recreate it from scratch.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inserting spreadsheet data rows as form fields in a document | b3nz | Word | 3 | 03-31-2013 07:47 PM |
Delete Blank Rows (Cyrillic Text in Spreadsheet ) | dozd | Excel | 1 | 02-22-2013 03:24 AM |
Display history for spreadsheet data | cjaye | Excel Programming | 11 | 05-08-2012 04:22 PM |
Displaying cell data in another spreadsheet | Colonel Biggs | Excel | 0 | 07-01-2010 07:37 PM |
Print with hidden cols? | markg2 | Excel | 2 | 01-06-2010 06:04 PM |