UsedRange와 VLookup 을 이용한 데이터 비교 로직

7개월 만에 글을 쓰는 군요.

그 동안 iOS  앱 개발 프로젝트에 참가하면서 글이 중단 된 거 같은데...

Mobile Automated Testing Framework이 없어서 열심히 Manual Testing을 하느라고 바빴습니다.

FleetService 와 관련해서 Driver 들의 주행 정보를 수집하고 Driver 들에게 필요한 정보들을 제공해 주는 앱을 개발하고 있습니다.

엊그제 Prem이라는 동료가 와서 부탁한게 있어서...

제가 엑셀 프로그래밍을 업무에 몇번 활용했더니 그게 소문이 났는지 저한테 엑셀 프로그래밍을 부탁하더라구요.

데이터소스에서 데이터 수천개를 뽑아서 A 컬럼에 넣고 또 수천개를 뽑아서 C 컬럼에 넣었는데..

A 컬럼에 있는 데이터들을 일일이 가져와서 C 컬럼 어디엔가 있는지 없는지를 확인해야 한다고 하더라구요.

로직을 잘 고민해 보니까 컬럼간 비교보다는 C 컬럼에 데이터를 다른 sheet에 넣어서 sheet 내의 데이터들을 비교하는 로직으로 만들면 코드가 훨씬 더 간편해 질 것 같더라구요.

예전에 사용해 봤던 UsedRange 하고 늘 사용하고 있는 VLookup 함수를 사용하면요...

그래서 이렇게 만들어 봤습니다.

Sub CompareData()
    Dim rngCell As Range
    For Each rngCell In Worksheets("Sheet1").UsedRange
        On Error Resume Next
        Sal = Application.WorksheetFunction.VLookup(Sheet1.Range(rngCell.Cells.Address), Worksheets("Sheet2").UsedRange, 1, False)
        If Err.Number <> 0 Then
            Let rngCell.Interior.Color = RGB(255, 36, 36)
        End If
    Next
End Sub

rngCell 이라는 Range 변수를 선언해 두고 For Each 문을 돌립니다.

rngCell In Worksheets("Sheet1").UsedRange

이렇게 돌리면 Sheet1 에 있는 각각의 데이터 수 만큼 For 문이 돕니다.

On Error Resume Next

이것은 에러가 났을 경우 에러처리 하지 말고 그냥 그 다음 것을 실행하라는 겁니다.

나중에 VLookup 을 했을 때 찾는 데이터가 없으면 N/A 로 에러 처리 되거든요..

그 때 catch  해서 그 이벤트를 handling 하려고 이렇게 했습니다.

Sal = Application.WorksheetFunction.VLookup(Sheet1.Range(rngCell.Cells.Address), Worksheets("Sheet2").UsedRange, 1, False)

VLookup 함수를 이용했는데요. For 문이 돌면서 Sheet1의 각 데이터가 하나씩 골라질 텐세 그 각 Range를 VLookup의 첫번째 파라미터로 놓고요. Sheet2 의 모든 UsedRange를 두번째 파라미터에 넣습니다.

세번째 파라미터 1은 첫번째 값을 가져 온다는 얘기고 마지막 False 는 exact match 를 사용한다는 겁니다.

이렇게 되면 Sheet1의 해당 데이터가 Sheet2에 있으면 그 값이 Sal 에 할당이 되고 없으면 N/A 이렇게 에러가 할당이 됩니다.

이 때 On Error Resume Next 를 하지 않았다면 그냥 에러 처리가 될 텐데 그게 있어서 그 다음 라인으로 넘어갑니다.

그 다음에 IF 문이 있는데요.

만약에 에러가 발생하면 해당 Cell의 Interior 색을 빨간색으로 하라는 겁니다.

그러면 Sheet1의 어떤 값이 Sheet2에 없을 경우 해당 Cell 이 빨갛게 칠해 지는 겁니다.

UsedRange와 VLookup 두개의 함수로 동료의 업무를 깔끔하게 해결해 줬네요.. :)