Scan D column until numbers no longer increase, keep last high value, delete rest, repeat

[johnstevens]

Hello everyone,

The title basically describes it all. Please refer to the attached picture for the more detailed version. Would anybody know how to get this done?


Sample.JPG

Thanks for all the help!

[apo]

H.. Thats an interesting requirement and in actual fact.. you image does show well what you want..

Having said that.. can you attach a Workbook with your data so we can test any solution that we may have.. (Workbooks are always preferred over images)..

[walruseggman]

This is a brute force method, there might be a more efficient way using vlookup or .Find or something, I'm just not familiar with searching for something like "< Value - 0.1".

And to echo apo, an example workbook with before and after's would help in testing the code.

Please Login or Register  to view this content.

[johnstevens]

walruseggman , thank you for your hard work on the code. It did seem to work at first, but then there was a mismatch type error. You and apo are correct and I do appologize for not providing a workbook. So here it is for you to fool around with. I really do appriciate the help and hard work!

[walruseggman]

The error is because some of your cells contain "<null>" (like around row 164), and obviously it's nonsensical to try to evaluate if a number is less than a string of text. I've accounted for that in the code, but would need to be more general if there can be other non-numbers.

I ended up with 108 rows of data after running this on your test file.

Please Login or Register  to view this content.

[johnstevens]

Great work on the code walruseggman! It does exactly what I explained for it to do. Unfortunately, there are some exceptions in the data that are causing the code to grab the wrong line and delete the correct one. Some of the rows that remain after running the code have zeros in the B, C, and G columns which isn't the data needed. These columns need to show maximum values when the D column has its maximum and resets.

It also seems like the D column number will decrease by more than 0.1 then increase before finally decreasing steadily again. So there is sometimes a "bump" in the data resulting in the return of more rows than needed. This isn't a huge issue as I can go through these manually and delete them. The issue is deleting data that is needed. Would it be possible to add some kind of buffer zone before deleting values? So find the correct row and also keep the row before and after while deleting the rest? Then I could go through manually to pick out the correct rows since the amount of data is more manageable. It's not ideal, but that way I would be sure all data is accounted for and correct. Unless of course you know of a better suggestion.

I really appreciate your help with everything so far walruseggman!

[walruseggman]

find the correct row and also keep the row before and after while deleting the rest?

Try this:

Please Login or Register  to view this content.

[johnstevens]

Thanks walruseggman! That will do nicely. I appreciate the help.