单列表重复项检测：基于出现顺序在第二列展示重复值或n/A

Alright, let's tackle this problem where you want to identify duplicates in Column A only when they've already appeared in rows above the current one, and output the duplicate value in Column B (or n/A if it's a first occurrence). Here's how to do it step by step:

Basic Formula (Works for All Excel Versions) #

Start with cell B2 (assuming your first data entry is in A1; if A1 is a header, start at B3). Enter this formula:

=IF(COUNTIF(A$1:A1,A2)>0,A2,"n/A")

Then drag the fill handle down Column B to apply it to all rows.

Breakdown of the Formula: #

• A$1:A1: This is a mixed reference—locking the starting row ($1) means when you drag the formula down, the range will automatically expand to include all rows above the current one. For example, when you get to B7, the range becomes A$1:A6.
• COUNTIF(A$1:A1,A2)>0: Checks if the value in the current A cell exists anywhere in the rows above it.
• IF(...): Returns the value from Column A if it's a duplicate, otherwise outputs n/A.

Example Result #

Here's how your data would look after applying the formula:

Case-Sensitive Duplicate Check (If Needed) #

If you need to treat uppercase and lowercase values as distinct (e.g., "Car" vs "car"), use this formula instead:

=IF(SUMPRODUCT(--EXACT(A$1:A1,A2))>0,A2,"n/A")

• EXACT(A$1:A1,A2) compares values case-sensitively, returning TRUE for matches.
• -- converts those TRUE/FALSE values to 1s and 0s, so SUMPRODUCT can count how many matches exist.

内容的提问来源于stack exchange，提问作者user3541631