Automate a task of creating new values with a interval of 3 in between the given table from and two values in excel
Budget: ₹1,500 – ₹12,500 INR
I have a excel with 2 tables. First table defines the FROM and TO value range and second table has rows with some values (lets call them depth values) with interval of 10 in between the range given in 1st table (Kindly see the attachment with sheets named Table 1 and 2).
Now I need to create a new 3rd table which will have a interval of 3 in between the range of depth values of the second table (table which has a interval of 10). But this time the condition is we have to assign the depth value to this third table in such a way that the upper limit and lower limit depth value of second table rows should be divided in a linear way in intervals of 3.
For ex. if the value range is between 1.5 to 1.8 for a interval of 10 in between 110 to 120 range then depth values with interval of 3 in between 110 and 120 should look like 1.6 for 113 and 1.7 for 116.
Also there a couple of conditions to generate the data which are given below
Condition 1: Table 2 may have depth values which may go out of range given in Table 1, but it should be assured that range in Table 3 will not go beyond the range given in Table 1. So logically the depth values are to be taken from 2nd table given in intervals of 10 but the upper and lower limit will be set from range given in Table 1.
Condition 2: if the interval of 3 is greater than the difference between the last interval depth value and upper range of that 2nd table row then directly the next row will have the next range depth value of 2nd table (For ex. if the ranges with interval 3 in between 110 and 120 are 113, 116, 119 then 119 will not be calculated as 120-119 = 1 which is less than the interval of 3). So there will be only 2 intervals ideally in between (number ending with 3 and 6).
Now will give an example:
1st Table Structure: FROM CHAINAGE: 100 TO CHAINAGE: 300 (these are single row values setting the range for 2nd table). LETS CALL THESE RANGES AS CHAINAGES.
2nd Table Structure: 1st Row: CHAINAGE: 100 DEPTH VALUE: 1.6, 2nd Row: CHAINAGE 110 DEPTH VALUE: 1.9, 3rd Row: CHAINAGE: 120 DEPTH VALUE: 1.7
3rd Expected Output TABLE Structure: 1st Row: CHAINAGE: 100 DEPTH VALUE: 1.6, 2nd Row: CHAINAGE: 103 DEPTH VALUE: 1.7, 3rd Row: CHAINAGE: 106 DEPTH VALUE: 1.8, 4th Row: CHAINAGE: 110 DEPTH VALUE: 1.9, 5th Row: CHAINAGE: 113 DEPTH VALUE: 1.83, 6th Row: CHAINAGE: 116 DEPTH VALUE: 1.77, 7th Row: CHAINAGE: 120 DEPTH VALUE: 1.7
I have uploaded the tables in a single sheet with names Table 1, Table 2 and Expected Table 3
I need to automate this task and prefer to have a VB code written in excel developer to achieve this. With a flexibility to change the interval from 3 to any other number. If you have better or easier option please suggest.
Now I need to create a new 3rd table which will have a interval of 3 in between the range of depth values of the second table (table which has a interval of 10). But this time the condition is we have to assign the depth value to this third table in such a way that the upper limit and lower limit depth value of second table rows should be divided in a linear way in intervals of 3.
For ex. if the value range is between 1.5 to 1.8 for a interval of 10 in between 110 to 120 range then depth values with interval of 3 in between 110 and 120 should look like 1.6 for 113 and 1.7 for 116.
Also there a couple of conditions to generate the data which are given below
Condition 1: Table 2 may have depth values which may go out of range given in Table 1, but it should be assured that range in Table 3 will not go beyond the range given in Table 1. So logically the depth values are to be taken from 2nd table given in intervals of 10 but the upper and lower limit will be set from range given in Table 1.
Condition 2: if the interval of 3 is greater than the difference between the last interval depth value and upper range of that 2nd table row then directly the next row will have the next range depth value of 2nd table (For ex. if the ranges with interval 3 in between 110 and 120 are 113, 116, 119 then 119 will not be calculated as 120-119 = 1 which is less than the interval of 3). So there will be only 2 intervals ideally in between (number ending with 3 and 6).
Now will give an example:
1st Table Structure: FROM CHAINAGE: 100 TO CHAINAGE: 300 (these are single row values setting the range for 2nd table). LETS CALL THESE RANGES AS CHAINAGES.
2nd Table Structure: 1st Row: CHAINAGE: 100 DEPTH VALUE: 1.6, 2nd Row: CHAINAGE 110 DEPTH VALUE: 1.9, 3rd Row: CHAINAGE: 120 DEPTH VALUE: 1.7
3rd Expected Output TABLE Structure: 1st Row: CHAINAGE: 100 DEPTH VALUE: 1.6, 2nd Row: CHAINAGE: 103 DEPTH VALUE: 1.7, 3rd Row: CHAINAGE: 106 DEPTH VALUE: 1.8, 4th Row: CHAINAGE: 110 DEPTH VALUE: 1.9, 5th Row: CHAINAGE: 113 DEPTH VALUE: 1.83, 6th Row: CHAINAGE: 116 DEPTH VALUE: 1.77, 7th Row: CHAINAGE: 120 DEPTH VALUE: 1.7
I have uploaded the tables in a single sheet with names Table 1, Table 2 and Expected Table 3
I need to automate this task and prefer to have a VB code written in excel developer to achieve this. With a flexibility to change the interval from 3 to any other number. If you have better or easier option please suggest.