Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 07-24-2014, 12:36 PM
ghumdinger ghumdinger is offline Using functions to restructure data in spreadsheet from rows to cols Windows 7 Using functions to restructure data in spreadsheet from rows to cols Office 2010 (Version 14.0)
Advanced Beginner
Using functions to restructure data in spreadsheet from rows to cols
 
Join Date: Jul 2010
Posts: 64
ghumdinger is on a distinguished road
Default 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
Reply With Quote
 



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
Using functions to restructure data in spreadsheet from rows to cols Delete Blank Rows (Cyrillic Text in Spreadsheet ) dozd Excel 1 02-22-2013 03:24 AM
Using functions to restructure data in spreadsheet from rows to cols 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft