Forum Discussion
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...
Hi there,
Your approach with concatenating the warning code with the date and using a combination of VLOOKUP and IF sounds like a reasonable way to tackle this. Another option you might consider is using Excel's built-in conditional formatting combined with the COUNTIF function to dynamically highlight new warnings.
Here's a simplified step-by-step approach:
Concatenate Warning Code with Date:
In a new column, say Column C, you can use a formula like =A2&B2 (assuming Warning Code is in Column A and Date in Column B). This creates a unique identifier for each warning with its occurrence date.
Use COUNTIF:
In another column, say Column D, use a formula like =COUNTIF($C$2:C2, C2) to count the occurrences of each warning code. This formula counts the number of times the current warning code appears from the top of the list down to the current row.
Apply Conditional Formatting:
Select the entire range of your warning codes (Column A), go to "Home" -> "Conditional Formatting" -> "New Rule."
Use a formula to determine which cells to format, and input a formula like =D2=1. This will highlight only the first occurrence of each warning code.
This way, each week when you add new data, the conditional formatting will dynamically adjust to highlight new warnings.
As for creating a dynamic table showing the first date of occurrence, you might consider using a combination of the MINIFS function (available in newer Excel versions) or an array formula with MIN and IF to extract the first occurrence date based on the concatenated identifier.
Remember to adapt these suggestions to your specific data structure and Excel version. I hope this helps! :bar_chart::sparkles:
Your approach with concatenating the warning code with the date and using a combination of VLOOKUP and IF sounds like a reasonable way to tackle this. Another option you might consider is using Excel's built-in conditional formatting combined with the COUNTIF function to dynamically highlight new warnings.
Here's a simplified step-by-step approach:
Concatenate Warning Code with Date:
In a new column, say Column C, you can use a formula like =A2&B2 (assuming Warning Code is in Column A and Date in Column B). This creates a unique identifier for each warning with its occurrence date.
Use COUNTIF:
In another column, say Column D, use a formula like =COUNTIF($C$2:C2, C2) to count the occurrences of each warning code. This formula counts the number of times the current warning code appears from the top of the list down to the current row.
Apply Conditional Formatting:
Select the entire range of your warning codes (Column A), go to "Home" -> "Conditional Formatting" -> "New Rule."
Use a formula to determine which cells to format, and input a formula like =D2=1. This will highlight only the first occurrence of each warning code.
This way, each week when you add new data, the conditional formatting will dynamically adjust to highlight new warnings.
As for creating a dynamic table showing the first date of occurrence, you might consider using a combination of the MINIFS function (available in newer Excel versions) or an array formula with MIN and IF to extract the first occurrence date based on the concatenated identifier.
Remember to adapt these suggestions to your specific data structure and Excel version. I hope this helps! :bar_chart::sparkles:
Hello,
Thank you for your feedback. Unfortunately COUNTIF turns out to be hard on the hardware to handle as it amounts to way too much calculation being done in this particular file. 
Still, it is a very interesting (and straight to the point) method that will surely be useful in the future.
Combining MIN and IF also works great to pinpoint the first occurrence right way.
Thanks for the assistance!