Hi All,
I have a large data set in sheet1 which has ~20 columns and 10000+rows and each row has a set of limits based on what value is in column B this data set changes ~once per week.
The limits are stored in sheet2 where column A contains the unique list of values from column B in sheet1 and the min and max limits are stored over ~40 rows to the right of the value.
Easiest way I can think to explain is below example I have searched a lot and can't come up with the answer but I am currently doing this with index match then a formula to check if it is within the limits but it takes an age to load so i wish to do this with vba so i can update it as and when and if I need to leave it to run for a bit I can; but the sheet would then be usable and quick afterwards.
example sheet 1:
<tbody>
</tbody>
example sheet 2:
<tbody>
</tbody>
result:
<tbody>
</tbody>
I hope this makes sense as this problem is driving me crazy as it takes me a long time to do anything with the data as I have to review the data on an almost daily basis.
Thanks in advance to any help I can get as I am fairly new to vba but I'm sure this is where the answer lies.
Regards,
Matt
I have a large data set in sheet1 which has ~20 columns and 10000+rows and each row has a set of limits based on what value is in column B this data set changes ~once per week.
The limits are stored in sheet2 where column A contains the unique list of values from column B in sheet1 and the min and max limits are stored over ~40 rows to the right of the value.
Easiest way I can think to explain is below example I have searched a lot and can't come up with the answer but I am currently doing this with index match then a formula to check if it is within the limits but it takes an age to load so i wish to do this with vba so i can update it as and when and if I need to leave it to run for a bit I can; but the sheet would then be usable and quick afterwards.
example sheet 1:
header 1 | header 2 | header 3 | header 4 |
random value1 | A | 1 | 2 |
random value2 | B | 4 | 2 |
random value3 | A | 6 | 3 |
<tbody>
</tbody>
example sheet 2:
sheet 1 header 2 value | min limit for header 3 sheet 1 | max limit for header 3 sheet 1 | min limit for header 4 sheet 1 | max limit for header 4 sheet 1 |
A | 3 | 9 | 1 | 4 |
B | 3 | 7 | 5 | 8 |
<tbody>
</tbody>
result:
header 1 | header 2 | header 3 | header 4 | min limit for header 3 sheet 1 | max limit for header 3 sheet 1 | min limit for header 4 sheet 1 | max limit for header 4 sheet 1 | within limits header 3 | within limits header 4 | within limits overall |
random value1 | A | 1 | 2 | 3 | 9 | 1 | 4 | no | yes | fail |
random value2 | B | 4 | 2 | 3 | 7 | 5 | 8 | yes | no | fail |
random value3 | A | 6 | 3 | 3 | 9 | 1 | 4 | yes | yes | pass |
<tbody>
</tbody>
I hope this makes sense as this problem is driving me crazy as it takes me a long time to do anything with the data as I have to review the data on an almost daily basis.
Thanks in advance to any help I can get as I am fairly new to vba but I'm sure this is where the answer lies.
Regards,
Matt