Excel. How to format data in rows automatically.

Hi all.
I am a relative noob when it comes to Excel, so I hope there is someone who can help me out with a probably trivial problem.

Thing is, I am logging data to a CSV file. All the data is added sequentally to the file.
If I open it with Excel it looks like this:

VarName       TimeString         VarValue

Test_tag_00   20-10-2005 13:41   1

Test_tag_01   20-10-2005 13:41   2

Test_tag_02   20-10-2005 13:41   3

Test_tag_03   20-10-2005 13:41   4

Test_tag_00   20-10-2005 13:48   1

Test_tag_01   20-10-2005 13:48   2

Test_tag_02   20-10-2005 13:48   3

Test_tag_03   20-10-2005 13:48   4

I would like it much more if I could convert it to something like this:

TimeString        Test_tag_00 Test_tag_01 Test_tag_02 Test_tag_03

20-10-2005 13:41  1           2           3           4

20-10-2005 13:48  1           2           3           4

But how can I achive that ?
With some clever sorting or a macro ?

Edit: it looked awful at first. Its not so easy to paste directly from Excel.

Hi Jesper

In Excel help, search for:

'Transpose rows to columns or columns to rows'

It is better to change the CSV file.

Henry

krk, I will look into that function in excel. Thanks.

Henry, I cannot change the format of the CSV file. The format is decided by the program that logs the data.

Transpose is no good, because it only converts rows to columns and vice-versa. From the looks of what you have and what you want, there is more needed than transpose: the data has to be rearranged completely. I think macros will be needed.

Kind regards,

An easy workaround until you have time to write VB code or macros. Link the data in a regular excel workbook cell to the cells in the csv. Click on the cell in your xls file where you want the first item to display and hit "=". Click on the source cell in the csv file and hit "enter". Do this for the rest of your data. When you get done, save the file in the same directory with the csv. When you open the file with links to the csv file, you will be prompted to update it with the data from the csv. The csv file must be already open or you'll get an error. Another drawback to this is that is only updated when opened. There are ways to automate that, but then you will need a little VBA or at least DDE.

Hope this helps.

Maybe an option

I am going from memory on this.
Do you have a choice of narrow or wide format for logging? It looks like you are logging in narrow format and you would like wide. I know RSView32 gives this option but yours may not.
Bob O

Using 'Pivot Tables' in Excel I was able to massage the data to something like the format you need. You may want to investigate that.

Jesper,

Is the information in one text string or is it in 3 seperate strings?

I've done mailing list macros that do the same transposition stuff

If you pasted the Excel file into say Cell B2 then the transposed stuff would appear begining at Cell F2

Now that I have thoughly confused you just send me a 'Real' Excel file and I'll massage it for you

Rod

Jesper

I am the past master on this dilemna. Had 20 Meg of raw data that looked like yours. Had to have UW computing write a program to put it in columns. They said 24 hours - one week later "here it is - lot harder than I thought.

At the minimu it looks like you are taking 4 data points every 7 minutes. That is a lot of shuffling of data. The two options are to use "data sort" and that will at least get all point 1's clumped together
TWO CAUTIONS
1 Save the file as a "master" and "working" - when you screw it up doing this on the first few times you can always go back to the master. Pain in the but I know --
but you will forget to do #2.

2. Make sure you ALWAYS sort all the columns (that is why the master)

Write down all the steps you need to do and then write the macro to save you the scut work.

BETTER yet can you get the data logger to put this in columns for you in the first place. Whose data logger are you using - uh is it a data logger?

Gonna have to think on this some more.

Dan Bentler

JesperMP said:

Hi all.
I am a relative noob when it comes to Excel, so I hope there is someone who can help me out with a probably trivial problem.

Thing is, I am logging data to a CSV file. All the data is added sequentally to the file.
If I open it with Excel it looks like this:

VarName TimeString VarValue

Test_tag_00 20-10-2005 13:41 1

Test_tag_01 20-10-2005 13:41 2

Test_tag_02 20-10-2005 13:41 3

Test_tag_03 20-10-2005 13:41 4

Test_tag_00 20-10-2005 13:48 1

Test_tag_01 20-10-2005 13:48 2

Test_tag_02 20-10-2005 13:48 3

Test_tag_03 20-10-2005 13:48 4

I would like it much more if I could convert it to something like this:

TimeString Test_tag_00 Test_tag_01 Test_tag_02 Test_tag_03

20-10-2005 13:41 1 2 3 4

20-10-2005 13:48 1 2 3 4

But how can I achive that ?
With some clever sorting or a macro ?

Edit: it looked awful at first. Its not so easy to paste directly from Excel.

Click to expand...

Jesper
Forgot -- the other option is to use "copy paste paste special transpose"

I do not use this for two reasons
1. It scres up the formulas (not your problem cause you have just values)
2. It is a royal pain to keep track of how many columns and lines you need for new data placement.

Dan Bentler

Some more info.

Thanks for all the responses so far.

A little info.
The datalogs are from Win CC Flexible RT.
Cyclical timed datalogging, alarm mesaages, system messages are logged and displayed very easily. That is covered and I dont have to do anything extra.
But in addition to the above I want to log data when a batch sequence is finished with all the relevant data for that batch (in other words logging on event, not timed) . Win CC Flexible doesnt support this that well, but I have managed to get the logging running.

Next I want to be able to view the logged data, and as I cannot do that from within Win CC Flex, I am thinking about using Excel. With Excel I can view the data and graph it (at least thats the goal).

I have attached a sample Excel file with the original data format.

Bernie, I noticed that there are something called pivot tables, but what is it, and how do I use it.
(Maybe the above reveals how a complete noob to Excel I really am).

Leitmotif, that it took a specialist one week to figure it out sounds a little scary.
Can you tell me a little more about that "copy paste paste special transpose" ?

I am open to any suggestion that will allow me to view my data.
I should mention that it is also possible to log to a database. Would that be easier in terms of getting the data sorted and displayed ?

Sorted !

Yessss !! .. Pivot tables and Pivot charts does the trick.

Yesterday I knew nothing about pivot tables.
Today I am a seasoned veteran .

Well, actually it is only thanks to THIS magnificient talking animated tutorial (9 minutes) from Miricle Solutions.
Try it ! I got nowhere when looking thru the online help in Excel, but the tutorial walked me through the whole thing.
I have bookmarked that page as they have other tutorials as well (probably just as fantastic as the one about pivot tables).

nice excel forum

Jesper,
there is also a pretty good forum dedicated to excel. You can find it here --> http://www.excelforum.com/


-Mike

Thanks Mike,
actually there are quite a few. If you google for "Excel forum" there will be a long list of suggestions.

It appears that there are excellent forums for almost everything out there.

I found the aforementioned tutorial with some help from some guys on another forum (www.exceltip.com).