Forum Discussion

Gerdays's avatar
Gerdays
Copper Contributor
Nov 23, 2023

Identify new items in a weekly updated archive

Hello,   I am working on a file that lists warning messages with a unique identifying code for each. Every Monday, I do a new extraction that is added to the main archive, and add the date of the e...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 25, 2023

Gerdays 

You can achieve this by using a combination of Excel functions such as IF, COUNTIFS, and VLOOKUP. Here is a step-by-step guide:

1. Concatenate Warning Code and Date:

    • Assuming your unique identifier is in column A (Warning Code) and the date of extraction is in column B, you can concatenate them using a formula in a new column. Let's say you place this formula in column C:

=A2 & "_" & TEXT(B2, "yyyy-mm-dd")

This formula combines the warning code and the date in the format "WarningCode_Date."

2. Identify New Warnings:

    • In a new column, you can use the following formula to identify whether a warning is new:

=IF(COUNTIFS($C$2:C2, C2)=1, "New", "")

This formula checks how many times the concatenated identifier has appeared up to the current row. If it's the first occurrence, it marks it as "New."

3. Find First Date of Occurrence:

    • To find the first date of occurrence, you can use a combination of MIN and VLOOKUP. Assuming you want to display the first date in a new column (let's say column D):

=IFERROR(MIN(IF($A$2:A2=A2, $B$2:B2)), "")

This formula finds the earliest date for a particular Warning Code.

Here's a breakdown of how these formulas work:

  • COUNTIFS: Counts the number of occurrences of a specific identifier up to the current row.
  • IF: Checks if the count is 1, indicating the first occurrence.
  • MIN(IF(...)): Finds the earliest date for a specific Warning Code using an array formula.

Make sure to adjust the cell references based on your actual data location. After setting up these formulas, you'll have columns indicating whether a warning is new and the first date of occurrence.

Remember to adjust these formulas based on the actual layout of your data in your workbook. The text, steps and functions were created with the help of AI.

 

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Resources