엑셀 중복 제거 후 최신값만 남기기 — ID별 마지막 행 추출(UNIQUE·MAP·XMATCH·Power Query)
요약: “고객/상품/티켓(ID)마다 가장 최근 행만 남기고 싶다”가 요구사항의 핵심이다. Excel 365라면 UNIQUE로 ID 목록을 만든 뒤 MAP+FILTER+SORT 또는 XMATCH(search_mode -1)로 마지막 위치를 찾아 행 전체를 가져오면 된다. 데이터가 크면 Power Query의 Group By → Max → 머지가 가장 견고하다.
1) MAP/FILTER/SORT 방식(가독성 최고)
표 이름이 Tbl, 열이 ID, 날짜, 값라고 가정한다. 각 ID 묶음을 날짜 내림차순으로 정렬해 마지막 1행만 뽑는다.
=LET(
ids, UNIQUE(Tbl[ID]),
hdr, Tbl[#Headers],
result, MAP(ids, LAMBDA(i, TAKE(SORT(FILTER(Tbl, Tbl[ID]=i), XMATCH("날짜", Tbl[#Headers]), -1), 1))),
VSTACK(hdr, result)
)핵심은 FILTER(Tbl, Tbl[ID]=i)로 그룹을 만들고, SORT(...,-1)로 날짜 내림차순 → TAKE(...,1)로 첫 행 유지다. XMATCH로 날짜 열 번호를 찾아두면 열 순서가 바뀌어도 안전하다.
2) XMATCH로 ‘마지막 위치’를 찾아 INDEX
각 ID의 마지막 위치(아래쪽부터 찾기)를 XMATCH(search_mode,-1)로 구하고, INDEX로 행 전체를 가져오는 방식이다.
=LET(
ids, UNIQUE(Tbl[ID]),
hdr, Tbl[#Headers],
rows, BYROW(ids, LAMBDA(r, INDEX(Tbl, XMATCH(INDEX(ids,r), Tbl[ID], 0, -1), ))),
VSTACK(hdr, rows)
)search_mode = -1은 아래에서 위로 검색한다는 뜻. 동일 ID가 여럿일 때 가장 마지막(보통 최신 날짜) 행이 선택된다. 날짜 정렬이 보장되지 않으면 먼저 원본을 SORTBY(Tbl, Tbl[날짜], 1)로 정렬해 두는 것이 안전하다.
3) Power Query — Group By + Max
- 데이터 → 테이블/범위에서 → 쿼리 열기
- ID로 Group By → Max of 날짜 열 추가
- 원본과 머지(ID=ID, 날짜=Max 날짜)
- 머지된 열을 한 행만 확장 → 닫기 & 로드
새로고침에 강하고 수십만 행에서도 안정적이다.
4) 피벗 대안(요약 + 원본 연결)
피벗에서 행: ID, 값: 날짜(최대)로 요약한 뒤, 그 결과를 원본과 XLOOKUP으로 조인해 다른 열을 붙인다.
=XLOOKUP(1, (Tbl[ID]=A2)*(Tbl[날짜]=B2), Tbl[값])5) 검증/운영 체크리스트
- 날짜가 텍스트면 정렬이 무너진다.
NUMBERVALUE나DATEVALUE로 정규화. - ID·날짜가 동일한 중복행 처리 정책(첫 행/마지막 행/집계)을 미리 정한다.
- 표(Ctrl+T)로 만들고 구조적 참조를 사용해 범위 자동 확장.