Find and handle duplicate records in Excel

작성자: AILesson7 분 소요테스트::ChatGPT검토일: 2026-08-28

빠른 답변

Define duplicate identity, detect exact and near matches, and preserve conflicts for review. 제공할 내용: Duplicate definition, Columns and sample duplicates, Resolution policy. 예상 결과: Duplicate flags, resolution groups, and a non-destructive review workflow.

맥락 추가

텍스트는 이 브라우저에 유지됩니다. AILesson Prompts는 이를 모델이나 서버로 보내지 않습니다.

Duplicate definitionColumns and sample duplicatesResolution policy

프롬프트

채워지지 않은 필드는 플레이스홀더로 표시되므로 프롬프트를 복사하고 편집할 수 있습니다

Design an Excel workflow to find and handle duplicate records.

Duplicate definition:
[identity]

Columns and representative duplicate cases:
[layout]

Resolution policy:
[policy]

Distinguish exact duplicate rows, repeated business keys, and possible near matches. Do not recommend Remove Duplicates until the identity rule and conflict policy are explicit. Return helper formulas or conditional-formatting rules, duplicate group IDs, conflict checks, and separate actions for safe exact duplicates, conflicting duplicates, and uncertain near matches. Preserve source rows and show how to reconcile counts before and after resolution.

Playground에서 사용해 보기

기본적으로 비공개프롬프트 구성은 브라우저에서 로컬로 이루어집니다. 조직에서 허용하지 않는 한 기밀 정보를 AI 서비스에 입력하지 마세요.

입력에서 결과까지

적용 예시

구체적인 맥락이 이 레시피를 바로 사용할 수 있는 결과로 바꾸는 방법을 확인하세요

실제 입력

Duplicate definition: For a contact export, normalized email is the business key. Two rows with the same email are possible duplicates even if name capitalization differs. Rows with blank email are never auto-matched by name alone.
Columns and sample duplicates: A RowID; B FullName; C Email; D UpdatedAt; E Source; F Phone. Examples: R10/Ana/ANA@example.com/1 Jun/Web/123; R14/Ana Pérez/ana@example.com/4 Jun/CRM/blank; R20/Ana Perez/blank/5 Jun/Event/123.
Resolution policy: Exact identical rows may be marked redundant. For the same normalized email, retain the latest nonblank values only after conflicts are shown. Blank-email near matches require human review. Never delete the raw sheet.

예시 출력

Create G NormalizedEmail with =IF(C2="","",LOWER(TRIM(C2))); H EmailCount with =IF(G2="","",COUNTIF($G:$G,G2)); and I DuplicateGroup with =IF(H2>1,"EMAIL-"&G2,""). Blank emails stay ungrouped. Add conflict flags such as =IF(I2="","",COUNTA(UNIQUE(FILTER($F:$F,$I:$I=I2)))>1) for nonblank phone values, adjusted to exclude blanks if needed.

R10 and R14 join the same email group; they are not safe deletions because name, timestamp, source, and phone completeness differ. R20 remains an uncertain near match and receives ManualReview rather than the email group. Create an action column with ExactRedundant, MergeReview, NearMatchReview, or Unique. Keep Raw_ReadOnly, produce a separate resolved table, and maintain a ResolutionLog containing removed RowID, retained RowID, rule, reviewer, and date. Reconcile raw rows = resolved rows + exact redundant rows + merged rows retained in the log; also compare the unique normalized-email count before and after.

효과가 있는 이유

1
Defining identity before deletion prevents legitimate repeated events from being lost.
2
Grouping conflicts keeps evidence available for a human merge decision.

결과 확인

Does the duplicate key reflect business identity rather than visual similarity?
Are conflicting values preserved and assigned to review?
Can removed rows be traced to a retained record?

안심하고 사용하세요

자주 묻는 질문

이 레시피를 언제 사용해야 하는지, 무엇을 제공해야 하는지, 그리고 어떤 부분에서 사람의 검토가 여전히 중요한지에 대한 실용적인 답변

What should I prepare before using “Find and handle duplicate records in Excel”?

For “Find and handle duplicate records in Excel,” prepare Duplicate definition, Columns and sample duplicates, and Resolution policy. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Find and handle duplicate records in Excel” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—Duplicate flags, resolution groups, and a non-destructive review workflow—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Find and handle duplicate records in Excel”?

The published test record for “Find and handle duplicate records in Excel” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

더 많은 탐색 방법