Help with writing VBA-- is it possible for the following steps

[kd123]

Hi,

I am new to VBA and macros but starting investigating to see if it is possible to automate a data cleaning and analysis process that I have to do repeatedly for different excel files.

I was curious if it is possible to automate the following steps:

1. Copy and paste the EDA data from the parent and child into the same excel file (Copying information from one data file into another-- this is something that I would probably have to do myself)
2. Delete the second and third cells down that have 0 and 4 in their boxes (shift cells up)The first cell is a time stamp; convert it to a time using the following formula: =(A1/86400) + DATE(1970, 1, 1) + (-5/24)
3. Once this formula is entered, change the cell to a “Time Cell” and you should get a time stamp
4. Once you have a time stamp for both the child and parent, you want to line up their cells so that they are corresponding to the same part of the interaction. Whoever’s wristband started first will need their EDA data shifted down to align with the other partner (so that the same time is being captured for the partners) To determine how many cells to shift it down, do the following:
   1. Calculate the time difference between when each watch was turned on
   2. Convert that amount of time into seconds
   3. Multiple by the number of second by 4 to get the number of cells
   4. Shift the EDA data that had a earlier start time down by that many cells
5. Now that the cells are aligned, the next step is to delete invalid data
6. For both columns of data delete any value below .01
7. Next, determine when the interaction took place (what data are we going to use).
8. The end of the interaction should be 20 seconds from the end of the last wristband to be turned off (80 cells), so highlight the interaction beginning 80 cells up and continue for 1680 cells (7*60*4) to get 7 minutes of data
9. With the 7 minutes of EDA for the parent and child, the next step is to bin the data into 2-second bins
10. Be sure that before you bin the data, the very first row is the EDA data you’re using (otherwise the formula gets mad) Use the following formula for the parent and child data (separately): =AVERAGE(OFFSET(INDIRECT("B"&ROW(B1)*8-7),,,8))

1. You should end up with 210 data points for each partner

I don't know if it's possible, but even just automating some part of this process (such as delete all values below .01) would save me so much time. I would be very grateful to get whatever help can be offered since my computer skills are pretty bad. Thank you so much, once again I appreciate any help.

 

[Rijnsent]

Hi kd123,
what you describe sounds possible with VBA. What is your input? As in: you seem to be using 2 files with data, what format are they and can you see from e.g. the file names that they belong together? And at the end of your routine, what is the desired output: data in your sheet? Or a 3rd file with the processed data in a certain format? If you could provide some (mockup) data that would be really helpfull in helping you.
Cheers,
Koen