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

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.