VBA to sort by timestamp, insert decimal, and transpose to new worksheet

**blueflwr19** · 11-10-2014, 05:46 PM

Hi! I have a data acquisition log that I am trying to make more user friendly so that I can analyze the data. The DAQ output is listed in rows with 35 tags (for example), a time stamp, and values for each tag. The original log is column A: tag name, column B: time stamp, column C: value. I'd like to have a macro with a button that transposes the tags into columns (B:XX, for example), with column A as the time stamps, and sorts the data so that for each time stamp, the correct value is placed in the rows beneath the columns with the tag. Unfortunately, the number of tags varies slightly, due to a peculiarity of the DAQ, so the # of tags will probably be between 25 and 35 tags. I'd also like the macro to insert a decimal 1 space from the right of the value string before it's pasted into the correct column. I've attached a sample file of the DAQ log. Thanks!

**blueflwr19** · 11-10-2014, 05:50 PM

Sample file from DAQ:

test2.xlsm

**Crooza** · 11-10-2014, 07:04 PM

Hi,

I havn't created a VBA yet but wanted to make sure this is what you wanted as far as output is concerned.

Let me know if teh ouput tab is how you want your data. In regards to moving the decimal point to the left I simply divided by 10. I guess this is what you wanted

test2(1).xlsm

**blueflwr19** · 11-11-2014, 11:44 AM

Crooza, the output worksheet is perfect. That's exactly what I'm looking for re: the layout. It'll make the data easier to analyze (especially when there's 20k+ lines!). is it possible to do that with VBA? What happens when there is no value for that column tag? Does it leave a 0 or a blank space? (These questions are just curiosity!)

I was hoping that I wouldn't have to take the 35 columns of data, and create 35 more columns to do the =value(B2)/10. Is that what you did?

**Crooza** · 11-11-2014, 05:09 PM

Glad its what you want. All I did was created one additional column divided by 10 to move the decimal then created a pivot table. If you can do this that's great just record a macro the first time you do it. Otherwise let me know and I'll put something together

**blueflwr19** · 11-11-2014, 05:27 PM

Hmm... I'm attempting the pivot table, but I'm having trouble getting actual values in the pivot table. What does the "sum of VarVal /2" do in your pivot table? Also, how do I record a macro from the pivot table? Thanks so much for the help.

**Crooza** · 11-11-2014, 06:51 PM

When you create a pivot table it wants to summarise the information you have in the table. For this table the columns are your variables, the rows are your time and for each variable name and time you have ONE variable value. In a lot of situations you'll get mulitple variable values for a given time and variable name so Excel wants to know how it will summarise more than one piece of data into what can only be displayed as one cell. It gives you the option of Max, Min, Sum , Average etc. In your case this is academic as the sum of one cell is the same as the avergae and the min and the max so it doesn't make a lot of difference what you put in there.

The best way to record the macro is to go to the developer tab, press record macro, select the columns from your raw data then choose insert pivot table then create the pivot table as described above with the time, variable name and variable values (remember to use the variable values with the adjusted decimal point) and then select stop recording. Give it a go but let me know if you can't work it out and I'll put something together for you

**Crooza** · 11-12-2014, 12:26 AM

test2(1).xlsm

Try this. Provided you keep your data in the same format ie same number of columns, titles and the same order this should work on any number of rows.

You can run the macro 'CreatePP' with the present set of data OR copy your larger dataset over the top.

It will firstly create the new column to tidy up the decimal point issue then it will create the pivot table on teh output worksheet.

**blueflwr19** · 11-12-2014, 11:07 AM

The file that you provided is great and works perfectly. I inserted about 40,000 rows into the original log worksheet and it added the decimal, sorted, and tabulated perfectly. Thank you so much for the help! This is huge for the amount of data that I have to analyze!

**Crooza** · 11-12-2014, 05:30 PM

Great. Glad it worked. If you haven't already done so you should mark this thread as solved now

VBA to sort by timestamp, insert decimal, and transpose to new worksheet

LinkBack

Thread Tools

Rate This Thread

Display

VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Re: VBA to sort by timestamp, insert decimal, and transpose to new worksheet

Thread Information

Users Browsing this Thread

Similar Threads

Insert incremental value or timestamp driven by last modified date of cell

[SOLVED] Insert timestamp when ticked....

Copy and sort timestamp date from noncontiguous rows to a column

How can I sort mostly 3 decimal with some 4 decimal numbers

[SOLVED] insert date timestamp to end of spreadsheet

Bookmarks

Bookmarks

Posting Permissions