Help Request - IF - Duplicate Values Present, Identify Dates Within 2 Days As Invalid

[FrenchyFri27]

Hi All,

Not sure if this is possible. Been a head scratcher for me for a while now.

I'm working with Part Number based data. Each part number is serialized. Data point has an associated date with it and a cost.

I need a formula in a cell which will take the largest value and ignores the lowest value when duplicate values are identified ONLY when those duplicates fall with

1-4 days of each other. This stipulates a system error, anything outside the 5 days is a valid data point. 

Example:

In the below example, the sum should exclude the 500 so that cell should be 0 because the bottom entry is 4500 and is the largest of the two within the 1-4 day range.

PN	SN	DATE	COST
12345	ABCD	1/16/2022	5000
12345	ABCD	7/22/2022	500
12345	ABCD	7/20/2022	4500

[#2 · Accepted solution]

@FrenchyFri27 

To achieve this, you can use a combination of the IF, COUNTIFS, and MAX functions in Excel.

Here is the formula you can use:

=IF(COUNTIFS(A:A, A2, B:B, B2, C:C, ">="&C2-4, C:C, "<="&C2+4, D:D, ">"&D2)=0, D2, 0)

Assuming your part number is in column A, serial number is in column B, date is in column C, and cost is in column D, you can place this formula in an adjacent column (e.g., column E) for each row of data.

Let's break down the formula:

• COUNTIFS(A:A, A2, B:B, B2, C:C, ">="&C2-4, C:C, "<="&C2+4, D:D, ">"&D2) counts the number of occurrences where the part number, serial number, and date fall within the 1-4 day range and have a cost greater than the current row's cost.
• The IF function checks if the count is equal to zero. If it is, it means there are no duplicates with a higher cost within the specified range, so it returns the current row's cost (D2). Otherwise, it returns 0.

This formula will identify the largest cost within a 1-4 day range and exclude any lower costs within that range. You can apply this formula to each row of your data to get the desired results.

Note: Make sure to adjust the column references (A:A, B:B, C:C, D:D) in the formula to match the actual columns in your worksheet.

[#3]

@FrenchyFri27 

Before I dig in, is it possible the data may do something like this?

[#4]

the below example, the sum should exclude the 500 so that cell should be 0 because the bottom entry is 4500 and is the largest of the two ?

can you give your expected reault based on those dataset from the Open thread post?

[#5]

Thank you, this worked! Appreciate the formula and the breakdown.

[#6]

Hi Patrick, NikolinoDE's response was sufficient. Appreciate you chiming in regardless.