Transposing Data from rows to columns

**Joe47** · 07-08-2013, 08:45 AM

Hi all,

I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.

The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.

I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.

I would be very grateful if someone could offer some advice as to how to speed up this operation.

Freezer Txt File.txt Transpose Spreadsheet.xls

**XOR LX** · 07-08-2013, 10:27 AM

Hi,

Can you explain in a bit more detail the choice of times to be output in the Final format?

Five-minute intervals appear to be the norm, though from your example it appears that you also want these to be aligned to clock times ending in 0 or 5, i.e. 14:30, 14:35, 14:40, etc., which means that the duration of the first interval (14:29-14:30) is deliberately shortened, in this case to one minute - is this always the case?

Also, in your Final format, why then are some (specifically, the last four) of your times (14:50:02, 14:55:04
, etc.) given as several seconds past the minute?

Regards

**Joe47** · 07-08-2013, 12:26 PM

Hi and thank you for your prompt reply,

I'm not sure what happened there! The time interval should be the same in all cases, i.e. 1 minute, I must have made an error somewhere when I was transposing the data - another good reason for automating the process I guess. We do use other time intervals, e.g. 30 secs, 1 minute, 5 minute etc.

What I haven't mentioned is that there is usually a large amount of unwanted data before and after the set of data that we use in our final report, however, the important thing at the moment is to get the data into columns and we can cover the selection of data at some other stage.

In all cases the time can be reduced to hh:mm as the seconds are not critically important to the final report.

I hope this clarifies my question.

Regards,

Joe.

**XOR LX** · 07-09-2013, 03:32 AM

Morning Joe,

By far the simplest solution is a Pivot Table. Not only does this perform the desired grouping, it also allows the transposition to the format you desire quite easily.

Pivot Tables can be refreshed when the data source changes. The source data range can also be overwritten with a new range whenever you desire and the values will update accordingly.

You can also convert/paste this into a standard table if you have certain presentation wishes (though there are also in-built styles/formats you may wish to explore within the Pivot Table options).

If, for whatever reason, you are not happy with a Pivot Table solution (though that would be a shame as it is ideal here), let me know and we can look at setting up an alternative, formula-based solution, though this really should not be necessary in this case.

Regards

**Joe47** · 07-09-2013, 04:21 AM

Wow, that looks great! Unfortunately I know nothing about Pivot Tables so I've no idea how that works. I'm a good study though so I've no problem reading through tutorials to find out more; any advice or links appreciated.

**XOR LX** · 07-09-2013, 04:41 AM

Actually that one isn't too complicated:

The Channel field goes into the Column Labels area.

The Time field goes into the Row Labels area. You can then group this selection (by Hours and Minutes in this case).

The Reading field then goes into the Values area.

That's it! But anyway, you'll still want to understand a bit more, so perhaps (you list 2010 as your version, though you've been posting 2003 files?):

http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/

Regards

**Joe47** · 07-09-2013, 05:34 AM

Thanks for the link and all of your help and advice.
We've a mish-mash of Excel versions in our office and to save complications with compatibility we all save as 2003. That said, I think everyone who works on this spreadsheet is now on 2010 so maybe it's time to break the old habit!