Excel Formula to Identify Duplicates and Insert Data

Objective

The goal is to identify duplicates in column E and, for each duplicate found, insert the corresponding data from column AI into the adjacent column AJ.

Formula Description

To achieve this objective, we will use a combination of the IF, MATCH, and INDEX functions. This formula will check for duplicates in column E and fetch the value from column AI corresponding to the first occurrence of each duplicate.

Excel Formula

The formula to be placed in cell AJ2 (assuming the data starts from row 2) is as follows:

=IF(COUNTIF(E:E, E2) > 1, INDEX(A:A, MATCH(E2, E:E, 0)), "")

Explanation of the Formula Components

1. COUNTIF(E:E, E2):

   • This function counts how many times the value in cell E2 appears in the entire column E.
   • If the count is greater than 1, it indicates that E2 is a duplicate.

2. INDEX(A:A, MATCH(E2, E:E, 0)):

   • The MATCH function finds the row number of the first occurrence of the value in E2 within column E.
   • The INDEX function then uses this row number to return the corresponding value from column AI (here assumed to be column A for the sake of this formula; make sure to adjust the references based on your actual layout).

3. IF(<condition>, <value_if_true>, <value_if_false>):

   • This entire structure allows the formula to return the fetched value from column AI when duplicates are found, or an empty string ("") when there are no duplicates.

Practical Example

Consider the following dataset:

When placing the provided formula in AJ2 and dragging it down, the results in column AJ will appear as follows:

Interpretation:

• For the first "Apple" in E2, the formula retrieves "10" from column AI.
• The second "Apple" in E3 again pulls "10".
• For "Orange" in E4, there are no duplicates, hence AJ4 is empty.
• The second "Banana" in E5 fetches "20" as the first occurrence.

Conclusion

The provided formula efficiently identifies duplicates in column E and retrieves the data from the first occurrence in column AI to column AJ. Ensure to adjust column references according to your actual data layout, specifically for columns AI and AJ. For further learning about Excel functionalities and advanced data analysis, consider exploring the Enterprise DNA Platform.