PDA

View Full Version : VBA - Finding and copy row with first value >&< certain threshold



tmvds
03-27-2016, 04:29 PM
Hi everyone, I am new to VBA and would really appreciate some advice with this task.

I have a database with a changing number of entries per day (column A - some days 5 entries, others 20 etc).
I need to find and copy into a new sheet the entire row, for each day, for the first entry that has a value in column B which is lower & greater than a threshold (let's say 70%).

To be clear, let's suppose 28th March has 5 entries and 29th March 3 entries:

1 28/03 90% A
2 28/03 73% B
3 28/03 68% C
4 28/03 65% D
5 28/03 50% E
6 29/03 74% F
7 29/03 50% G
8 29/03 65% H

I need the VBA code to find the first value above (73%) and below (68%), and copy these entire 2 rows in a new sheet. Then move on to 29/03, and copy rows 6 & 8 into the new sheet. The final result in the new sheet should be:


2 28/03 73% B
3 28/03 68% C
6 29/03 74% F
8 29/03 65% H


Any suggestion?
Thanks a lot for your help.

tmvds
03-28-2016, 01:29 AM
Just to specify, it can never happen that the same day has the same 2 entries.
So it is not possible to have:


1 28/03 68% A
2 28/03 73% B
3 28/03 68% C




To be clear, column D contains numbers too and I need, for each day, an interpolated value at 70%. Since there won't be an exact observation at 70%, I need to get the first value above and the first value below 70% and linearly interpolate.


for example:


1 28/03 90% 100
2 28/03 75% 80
3 28/03 65% 70
4 28/03 60% 40
5 28/03 50% 30


I will need to copy row 2 & 3 to a new sheet and then interpolate a value for 70%, which in this case will be 75. Possibly copy this entry into a new sheet too, so to have for each day the interpolated value at 70%.


NewSheet1 ===>


2 28/03 75% 80
3 28/03 65% 70


NewSheet2 ===>


1 28/03 70% 75






The two other issues I can think of are:
1) the distance of the observations above and below the 70% threshold are not always equal. One day can be 75%-65%, another 73%-69%. So to interpolate the exact 70% I will need to take this into account.


2) It can happen that somedays do not have observation above or below 70%. So if, for example, a day has ONLY obs above, I will need to get the first TWO obs above 70% and linearly get what should be the corresponding value at 70%.


ie:
1 28/03 90% 200
2 28/03 80% 150
3 28/03 75% 100


Delta 75-80%= 5% : 50=150-100 ===> 70% = 50


If needed I will try to be more exhaustive, thanks for your help. Much appreciated