SOLVED

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

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
5 Replies
best response confirmed by FrenchyFri27 (Copper Contributor)
Solution

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

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.

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

Before I dig in, is it possible the data may do something like this? # Re: Help Request - IF - Duplicate Values Present, Identify Dates Within 2 Days As Invalid

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?

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

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

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

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