Find Multiples and Min Within a Range

[GB]

Hi,
in the attached spreadsheet I have have array formula that searches for multiple values in column C that exist between 2 values in column B. This part of the formula works fine.

The problem I am now trying to solve is ...

When my formula finds more than 1 value in column C that exist between 2 values in column B then mark the non minimum value/s in column C with some text in column D.

My thinking is that the array formula needs to...

• evaluate if column C has a value, then,
• find the 1st row position of the number above in column B and the 1st row position of the number below in column B, then,
• search for multiples in column C between these rows positions from column B, then
• mark all multiple values from column C that are not minimum.

I look forward to what is possible.
regards
GB

 

[Somendra Misra]

Hi GB,

See the file. The formula is huge I am trying to put it in the repeated part in name manger. Will update soon.



Regards,

 

[GB]

Hi Somendra,
yes you are right, the formula is huge but working as spec'd. Dissecting this and trying to understand it will be a challenge. I see why you want to store part of the formula in the name manager. Great Job.

cheers
GB

 

[Somendra Misra]

@GB

See the file. Now it has 3 named range, please go through them

Regards,

 

[GB]

Hi Somendra,
if I add another value that is less than the others it is asking to delete this value, so there is a bug but I should be able to work it out from here. I will post a reply when I have done it. Don't hold your breath but I should get to it over the weekend.

I appreciate your effort on this.

kind regards
GB

 

[Somendra Misra]

The formula will say to delete for all the values which are greater than the MIN value. I think that was you requirement.

Regards,

 

[GB]

Hi Somendra,
I have attached another solution to the problem. In column D (highlighted green) my formula returns the expected results, ie if there are multiple values in column C that exist between the values column B then delete the values that aren't the minimum.

The interesting part of this solution is that I am using two helper columns in my formula in column D and it works perfectly. Change the values in column C (highlighted orange) and watch it behave correctly.

The formula in column E (highlighted red) doesn't work and I can't understand why. For this formula all I did was convert the helper columns into the name manager but the outcome of that doesn't make my formula in column E work correctly. I suspect it has something with the array entering or not???

There might be a shorter/smarter way of writing this and I am open to suggestions.

cheers
GB

 

[Somendra Misra]

Hi GB,

You are saying in file that you had uploaded in your comment #7, you have the right formula in column D. If so, than see the file I had uploaded in my comment #4 on sheet3 produces the same result as your column C result with the same data. And my formula don't use any helper cells but only 3 named formulas.

Kindly comment.

Regards,

 

[GB]

Hi Somendra,
if I use your solution (from comment #4) and change the value in cell C7 to 10000, your solution wants to delete both values in C7 & C17. In this case it should retain C17 which would be the minimum.

cheers
GB

 

[Somendra Misra]

Are you looking for this result, if so it's on Sheet3.



Regards

 

[GB]

Hi Somendra,
yes Sheet 3 returns the correct result, thank you. I was only looking at Sheet1. Your initial solution was so complex (to me) and inspired me to vary it to something that I would understand even though it raised other questions in terms of my named formulas not working.

Thanks for your help..... again.
regards
GB

 

[Somendra Misra]

Glad to help you out. Welcome back!!!

Regards,

 

[GB]

Hi,
just thought I would share another version of my final result which is a variation to Somendra's result. My final formula lives in column E and references the name manager and now ignores the helper columns. Yah.

regards
GB