How to search and remove a series of entries based on adjacent values.

Good morning,

I can't seem to solve my current formula problem.

I want to search two columns of data, and delete certain series of entries based on the average of the entries in the adjacent column.

Please see attached excel for example.

Thanks in advance for any help that may come!

Thanks for the quick reply!

But sorry, I don't usually have the entries in Column A, so it can't be referenced in the formula.
I always only have columns C and D to work with.

[I manually entered Column A entries to highlight what each "series" was]

Is there a formula that can be used referencing only Column C and D?

The formula would have to detect all instances of entries including the same XXXXX-XXX and treat them as one "series".

I have reattached the excel with only the usable data entered.

Thanks for your trouble.

Wow. That's almost perfect.

In row 2 I noted [There may be various hyphens/numbers/letters joined to the right.]

In row 6 to 10 I changed the cell A number adjoined to the right of *****-*** to a letter. The formula only returns a hyphen in cell D in this case. Can it be adjusted to return [Retain] even though the letters are joined in cell A?

Please see attached excel.

Hi,

The formula is returning [Delete] for a series if the first entry in cell B for the series is less than 20.

e.g. Series of "15263-001" is from row 6 to row 10.
If I change Cell B6 to "19" then [Delete] is displayed for this series in D6 to D10.

However the average of B6,B7,B8,B9 and B10 is still above 20. (213 / 5 = 42.6)
Because 42.6 is greater than 20 [Retain] should be displayed in D6 to D10.

In my excel attached to post # 7 I note in row 3;

"I want to delete a series if the average of the numbers in the adjacent cell B is less than 20."

Should I start a new thread?

edit# also in the first post I mention "average"

[I want to search two columns of data, and delete certain series of entries based on the average of the entries in the adjacent column.]

Thank you! I appreciate the help.

And sorry for ambiguity in my request. I am not so experienced with excel....

Hi Indi_Ra,

Thanks for your time.

In excel 4,
Unfortunately it seems that your formula decides values in column B are in the same series based only on the 3 digits in the middle. i.e. 001, 002, 851.

I need the formula to decide the series based on the 8 digits *****-*** i.e. 15263-001

This way, if another entry in column B has for example 45455-001 , it will be judged as a separate series.

Brilliant!