hi all!

This is the challenging task i have to solve... , the file can be downloaded he
http://www.fileswap.com/dl/nsdXSyLrk...52012.rar.html

explanation: For each day I receive several inputs of
listings in time , excel docs, that I have to organize them in one
unique result, applying some simple calculations:

My goal is to create one file per day, based on those daily inputs. I
copied these different excel docs across and organized them(all stati
values, no averages) and pasted in tab "draft" , "pressure" , "raw"
... they are all referenced with different time stamps but on the same
day:

draft tab draft is taken at a given random time, i.e. 06.13am, (the
only column to consider in the draft tab is "L") so, at 6.13 the draft
is -0.295

pressure tab some intervals: for example from 06:21 to 06:52 the
pressure is 1019.25 (consider just mean pressure)

raw tab for a whole given day I get a time and a value of tide ( a height)

I created then a "result" tab, (first tab) , where I joined all these inputs:
time and tide are the ones from the raw tab, then the pressure changes
according to the interval in "pressure tab" and draft changes
according to the interval in "draft tab" , I wasn't able to create a
VLOOKUP routine so what I did was to copy across manually the
intervals of "pressure" and "draft" into the "result" tab, ordering
them according to the timestamp in column A.
So for example, in pressure tab, the value of pressure from 06:21 to
06:52 , is 1019.25 ( to be considered only the mean pressure) so I've
manually located this interval in "resluts" tab (columnA) and pasted
the value 1019.25 all along the interval 06:21 to 06:52 , which is the
same for the whole interval.

... I did the same for draft.

The result is an "adjusted tide" based on some simple formulas, but
that's irrelevant.

As you can imagine, the copy and pasting manually from tabs to result
is incredibly time consuming....

Do you think that a VLOOKUP could help? or else any input is welcome.

Thanks again for any inputs in advance!

Dear contrucci, Good Afternoon.

I understood your question.
But remains one doubt.

At the interval of the first and the second line there is a gap of time missing.

06:21_06:52
?
08:14_08:31

What to do with 06:52_08:14 interval that is missing?

On your tab RESULTS you simply repeated the value of the preceding interval.

Is this a rule? You need to specify the rules of the process to be possible automatize it.

Name and Seq__________________________SOL___EOL___SOL___EOL _____Start_____End______ mean pressue
Pre-Trench_survey_STBY - 0001.db_________06:21_06:52_23,340_23,340_1019,600__1018,900___1019,25
Pre-Trench_survey_STBY - 0001.db_________08:14_08:31_23,329_23,348_1019,090__1018,620___1018,86
Pre-Trench_survey - 0002.db______________08:31_08:48_23,348_23,495_101 9,090__1018,620___1018,86
Pre-Trench_survey_STBY - 0001.db_________08:48_09:09_23,495_23,328_1019,090 __1018,620___1018,86
Pre-Trench_survey_STBY - 0002.db_________09:09_09:22_23,328_23,328_1019,090 __1018,620___1018,86
Pre-Trench_survey_STBY - 0001-0002.db_____12:32_13:01_23,329_23,324_1017,790__10 16,200___1017,00

I´m waiting your answer.

thanks a lot for the answer!!! sure I will explain better:

nothing, i am not interested in the missing interval but I have to fill it somehow in order to give it consistency so it can be filled with the value 'mean pressure' of the above interval, in this case, the missing interval
06:21_06:52
?
08:14_08:31
can be filled with 1019,25

and here lies the challenge.... the rule is that I am only interested to apply the value "mean pressure" in the 'pressure' tab into the 'results' tab , and only apply the the available value (1019.25) within the same time frame, and the missing time gab, can be filled with the value before or after...

another issue : I was also looking for a way to reduce the number of points in the tab 'results' , basically i just want to have 1 value per minute , and erase the duplicates..... any suggestions about this too? I tried with this =IF(MOD(ROW()-ROW(C$1),$D$1)=0,B6,NA()) which basically puts the value from column B into column C at the frequency indicated in cell D1, and as in my case, there are 6 entries per minute, i put 6... the problem is that it delivers repeated date cells every x interval... quite a pain... I tried also with an advanced filter, to remove duplicates... didnt work.

Dear Contrucci, Good Evening.

The data columns "draft" and "pressure" at your tab "results" are automated now.

Take a look at it and tell me if it worked for you.

http://www.4shared.com/zip/gcXfQGP1/...er_HELP_T.html


Have a nice day.

[quote=Mazzaropi;1601683]Dear Contrucci, Good Evening.

The data columns "draft" and "pressure" at your tab "results" are automated now.

QUOTE]

thank you Mazzaropi!!! that work perfectly, how did you do that?

if I have more entries of pressure or draft I just drag down the blue grid right?

thanks again! :)

Mazzaropi, actually I have another question, would it be possible to set the spread**** in such a way that the tab "results" will reflect only the data where there are values for the pressure?

in our exampe, I am interested to deliver data only from 00:00 to 13:19 (in tab "pressure", SOL and EOL times)

thanks again!

Dear Contrucci, Good Afternoon.

I´m not sure if I understood your new question.

Could you give me some example about what are you intending to receive.
Please attach a little part of the worksheet.
It´s easier to understand.