Need help to build Excell macro

Copper Contributor

Dear all,

I have a sample excell Sheet 1 in wich I have a bunch of data collected by an aquisition system. The date a represented as :

In A column: date and time of the sample
In B column value of the sample

 

I have 1000 records displays. The goal is that within this 1000 records, I need to display in Sheet 2 :

- Column  A the date and time  of sample at T0+60second of Shhet 1

- Column  B the Value  of sample at T0+60second of Shhet 1

 

In other words from the whole set of the 1000 records wich I have collected I need to extract one record every 60s

 

How cqn I do that with a macor, never doen this before and never use but if you provide sample I could understand

 

Thanks for help

regards 

9 Replies
Would be easier to use PowerQuery and then no code would be needed.

Convert data to a table, select data then from Insert tab select Table.
Place cursor in table and go to Data tab, then select get data from table.

When the query editor opens filter you column by 60 sec intervals, then click close and load.
And it will return data into a new tab with what you want.

Thnaks for your reply.

Will I have to be able to repeat that steps for all my sample each time ?

 

the final approach is that :

- I have an acquisition system which run for 30mn and collect a big bunch of data

- For this acquisition set I need to extract every 30s sample measure

 

Then I need to run the same process for an other set of 30mn acquisition and need to extract the same thing. 

 

At the end I  can compare each extracted values for each acquisition set.

 

I was thinking of using MACRO because the same operation will be done many time.

Does your approach with Power Querry run in similar , meaning that as soon it is created once I can apply it for other set of data without recreating it ?

 

regards

Its possible by using parameters, and passing the table name to the query.

Using Excel to do this seem like the wrong tool. I personally would fire this into a database and then you could manipulate in a multitude of ways to achieve the above requirements repeatedly and consistently without manual intervention ...
Thanks for you reply.
The problem le that the acquisition system whish le labview as only the way To export data in csg or excell. I am not able to control the way data gets collectés unfortunatly

Hello stephen

I have try what you sugest, I convert my data to table.

my data first column contains hh:mn:ss

 

how can I apply filter which takes every 30s sample interval  ?

at the end I should keep data from 30s, 1mn, 1mn30s, 2mn,.... until the end of recorded data

 

Thnaks for help

Add a new column that uses the Text.End(DateTime.ToTExt([Date]),5)) function that returns something like 00 AM, 10 AM, 20 AM, 30 AM etc then filter by 00 AM or 30 AM or 00 PM or 30 PM then remove the column.

 

I've attached an example

Here is another version with a parameter for selecting the table ... hopefully you get the idea

Hi stephan,

I have download your first sample but how did you generate the sheet2 from sheet1 ?

I cannot see the formular you have applied, can I see it somewhere or how to reproduce ?

 

Sorry for basic stuff but not so familar with this

 

Q : I I have a second set of aquisition data how can I apply same filter by default all time without rewriting it ?

Thanks for help

Try the Second spreadsheet, it allows you to select the table you want, using the parameter.

 

The Power Query generates the sheet automatically, so all you have to do is query the first table and it will generate the second.

 

On the right hand side there is a list of actions, each one is applied one after another ... click the cog next to them and it will open up their actions.

 

Alternately you can look at the whole query by using the Advanced Editor (on home tab, 3rd across in the middle) to see all the actions. One of the great things about open the Advanced Editor is you can copy this into another spreadsheet and paste into a blank query much like code. You would then need to change a couple of things, like the Table name and then click "Close & Load" and the new sheet will be generated.