VBA 두 행 비교 관련 질문입니다

VBA
작성자
이재인
작성일
2023-11-03 15:41
조회
221
엑셀버전 : 엑셀2019

운영체제 : 윈도우10

질문 요약 : VBA 두 행 비교 질문

현재 A행과 B행을 비교해서 A행에 있지만 B행에는 없는 부분을 C행에 추출하려고 하는데요.

A행의 수가 B행보다 많을 때는 잘 작동하는데

문제는 A행의 수 만큼만 B행과 비교하더라고요

두 행 중 많은 쪽의 수만큼 비교하려면 어떻게 수정해야할까요

아니면 꼭 많은 쪽이 아니더라도 전체를 다 비교하는 방법도 좋습니다.

도움 부탁드립니다.

 

Dim strSQL As String, strConn As String, T As String
Dim Rs As ADODB.Recordset
Dim n As Long

Application.ScreenUpdating = False
Range("G6:G" & Rows.Count).ClearContents
T = ThisWorkbook.FullName
n = Cells(Rows.Count, "B").End(3).Row
strSQL = "Select BarcodeNo "
strSQL = strSQL & "From (Select BarcodeNo From [BarcodeCheck$B4:B" & n & "]) As A "
strSQL = strSQL & "Left Join (Select ScanBarcode From [BarcodeCheck$D4:D" & n & "]) As B "
strSQL = strSQL & "On A.[BarcodeNo] = B.[ScanBarcode]"
strSQL = strSQL & "Where (B.ScanBarcode IS Null)"

strConn = "Provider= Microsoft.ACE.OLEDB.12.0;" & "Data Source= " & T & ";" & _
"Extended Properties= Excel 12.0;"

Set Rs = New ADODB.Recordset
Rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not Rs.EOF Then
Range("G6").CopyFromRecordset Rs
End If
Rs.Close
Set Rs = Nothing

신고
스크랩
공유
전체 8

  • 2023-11-03 16:39
    채택된 답변

    @이재인 님 목록의 비교는 Dictionary가 짱이죠...

    아래처럼 프로시저를 만들어 두면,,,

    Sub ExclusiveList(List1 As Variant, List2 As Variant, Target As Range, Optional CaseInsensitive As Boolean = True)
        '// 2개의 범위나 Array를 비교하여 결과를 하나의 열로 반환
        '// Target 셀부터 결과를 출력
        '// CaseInsensitive    True : Case-Insensitive, False : Case-Sensitive
        
        Dim ListA As Variant, ListB As Variant
        Dim oDicA As Object: Set oDicA = CreateObject("Scripting.Dictionary"): oDicA.CompareMode = IIf(CaseInsensitive, vbTextCompare, vbBinaryCompare)
        Dim oDicB As Object: Set oDicB = CreateObject("Scripting.Dictionary"): oDicB.CompareMode = IIf(CaseInsensitive, vbTextCompare, vbBinaryCompare)
        Dim vItem As Variant, rArray As Variant
        
        If TypeName(List1) = "Range" Then ListA = Intersect(List1.Parent.UsedRange, List1).Value2 Else ListA = List1
        If TypeName(List2) = "Range" Then ListB = Intersect(List2.Parent.UsedRange, List2).Value2 Else ListB = List2
        
        If IsArray(ListA) Then
            For Each vItem In ListA
                If vItem <> vbNullString Then oDicA(vItem) = CStr(vItem)
            Next
        Else
            If ListA <> vbNullString Then oDicA(ListA) = CStr(ListA)
        End If
        
        If IsArray(ListB) Then
            For Each vItem In ListB
                If vItem <> vbNullString Then oDicB(vItem) = CStr(vItem)
            Next
        Else
            If ListB <> vbNullString Then oDicB(ListB) = CStr(ListB)
        End If
        
        For Each vItem In oDicB.keys
            If oDicA.exists(vItem) Then oDicB.Remove vItem: oDicA.Remove vItem
        Next
        For Each vItem In oDicA.keys
            If oDicB.exists(vItem) Then oDicA.Remove vItem: oDicB.Remove vItem
        Next
        For Each vItem In oDicB.keys
            If Not oDicA.exists(vItem) Then oDicA(vItem) = oDicB(vItem)
        Next
        If oDicA.Count > 0 Then rArray = oDicA.keys
    
        If IsEmpty(rArray) Then GoTo EXIT_RUN
        If oDicA.Count = 1 Then Target.Cells(1,1) = rArray: GoTo EXIT_RUN
    
        rArray = Application.Transpose(rArray)
        Target.Cells(1, 1).Resize(UBound(rArray, 1), UBound(rArray, 2)) = rArray
        
    EXIT_RUN:
        Set oDicA = Nothing: Set oDicB = Nothing
    
    End Sub

    이렇게 사용할 수 있습니다.

    Sub Test()
        ExclusiveList Range("A1:A11"),  Range("B1:B8"),  Range("C1")
    End Sub
    

     


    • 2023-11-03 16:45

      @원조백수 님 답변 감사합니다!

      헌데 제가 관련 지식이 적어서 답변에 대해 이해를 잘 못하겠습니다....

      위의 Dictionary를 그대로 복사한 후 아래 ExclusiveList의 Range만 바꿔서 사용하면 되는건가요?

      아니면 DIctionary에서도 따로 범위 지정이 필요한건가요?


      • 2023-11-03 16:52

        @이재인 님 test()에 있는 범위만 바꾸시면 됩니다.


        • 2023-11-03 16:55

          @원조백수 님 매번 데이터의 수량이 바뀌는데 Data의 범위를 ("A1:A") 이런식으로 적용할 방법은 없을까요...

          추가로 A행에 있지만 B행에 없는걸 C행에, B행에 있지만 A행에 없는걸 D행에 기입하려고 합니다!


          • 2023-11-03 18:07

            @이재인 님 귀찮아서 드리는 말씀입니다.^^
            EGTools를 설치하시면 CompareList라는 함수가 있습니다.
            https://egtools.tistory.com/entry/COMPARELIST

            위에 드린 코드도 원래 소스에서 필요한 부분만 남긴 것인데,
            5가지 방법으로 비교한 목록을 출력해 줍니다.
            0=List1과 List2를 더한 항목들의 중복을 제거한 Unique한 항목
            1=List1에만 있고 List2에는 없는 항목
            2=List2에만 있고 List1에는 없는 항목
            3=List1과 List2 양쪽 모두에 있는 항목
            4=List1이나 List2중 어느 한 쪽에만 있는 항목 

            목록의 범위는 A:A처럼 지정할 수 있는데,  현재 사용중인 범위만으로 변경하여 적용해 줍니다.
            (위에 코드도 그렇게 되어 있습니다.)

                If TypeName(List1) = "Range" Then ListA = Intersect(List1.Parent.UsedRange, List1).Value2 Else ListA = List1
                If TypeName(List2) = "Range" Then ListB = Intersect(List2.Parent.UsedRange, List2).Value2 Else ListB = List2

  • 2023-11-06 16:30

    @이재인 님 좋은 정보 감사합니다.


  • 2023-11-03 16:30

    n = Cells(Rows.Count, "B").End(3).Row

    이 부분이 문제 같아서 현재 편법으로 다른 행에 1~3000까지 넣어두고 해당 부분 수량 세는 걸로 바꾸니까 제대로 작동하긴 하네요.

    근데 다른 방법이 있다면 부탁드립니다.ㅠㅠ


  • 2023-11-07 11:56

    @이재인 님 좋은 정보 감사합니다 🙂


전체 13,315
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[📚교재 출간 안내] 「진짜쓰는 실무엑셀」 , 드디어 출간되었습니다! (235)
오빠두엑셀 | 2022.02.03 | 추천 514 | 조회 259666
오빠두엑셀 2022.02.03 514 259666
공지사항 문서서식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (197)
오빠두엑셀 | 2021.10.28 | 추천 280 | 조회 17728
오빠두엑셀 2021.10.28 280 17728
64387 VBA
New 아실 크롤링 관련 문의 (1)
임정호 | 2023.11.30 | 추천 0 | 조회 22
임정호 2023.11.30 - 22
64384 VBA
New VBA PivotItem 문의 (1)
봉s | 2023.11.30 | 추천 0 | 조회 18
봉s 2023.11.30 - 18
64382 파워쿼리/피벗
New [파워쿼리] 쿼리안에서 구분기호 기준으로 열분할을 하고 난 후, 아래로 채우기가 안됩니다.!! (2) 답변완료
jja**** | 2023.11.30 | 추천 0 | 조회 25
jja**** 2023.11.30 - 25
64381 함수/공식
New row()가 자동으로 변경될 수 있도록.... 엑셀파일 (3)
대빵 | 2023.11.30 | 추천 0 | 조회 29
대빵 2023.11.30 - 29
64377 피벗테이블
New 엑셀 피벗 행/열 비율 산출 질문 (2)
bugko**** | 2023.11.30 | 추천 0 | 조회 18
bugko**** 2023.11.30 - 18
64376 VBA
New 견적서 내용을 리스트업 하는 VBA 엑셀파일첨부파일
조범주 | 2023.11.30 | 추천 0 | 조회 31
조범주 2023.11.30 - 31
64371 구글시트
New 두 개 시트 연동 (1)
RecSoo | 2023.11.30 | 추천 0 | 조회 26
RecSoo 2023.11.30 - 26
64370 함수/공식
New 모든시트를 선택하는 함수가 있을까요?ㅠㅠ (1)
myyh**** | 2023.11.30 | 추천 0 | 조회 36
myyh**** 2023.11.30 - 36
64367 VBA
New 특정셀에 여러 변수 값을 대입하여 나오는 계산 값을 변수에 맞게 한번에 출력되게 하는 방법이 있을까요?? 엑셀파일첨부파일 (2) 답변완료
박재관 | 2023.11.30 | 추천 0 | 조회 35
박재관 2023.11.30 - 35
64366 문서서식
New 전체인쇄와 시트1개 인쇄시 이미지 품질 저하문제 첨부파일 (2)
코코마 | 2023.11.30 | 추천 0 | 조회 18
코코마 2023.11.30 - 18
64360 함수/공식
New 각 열의 텍스트를 비교하여 일치율에 따라 관련열 값 가져오기 첨부파일 (1)
요코하마박 | 2023.11.30 | 추천 0 | 조회 34
요코하마박 2023.11.30 - 34
64359 구글시트
New 엑셀 또는 구글시트 - 다른시트 데이터의 새로운값만 순차적으로 가져오는방법.. (3)
myyh**** | 2023.11.30 | 추천 0 | 조회 25
myyh**** 2023.11.30 - 25
64355 함수/공식
New sumifs 문의 드립니다. (2) 답변완료
sarangh**** | 2023.11.30 | 추천 0 | 조회 29
sarangh**** 2023.11.30 - 29
64353 함수/공식
New 엑셀 수식이 안 먹어요~ 엑셀파일 (2)
안녕 | 2023.11.30 | 추천 0 | 조회 44
안녕 2023.11.30 - 44
64352 VBA
New 명령단추 클릭시 색상변경 엑셀파일첨부파일 (2)
본아이디 | 2023.11.30 | 추천 0 | 조회 34
본아이디 2023.11.30 - 34
64351 VBA
New Sendkey 초기화 관련 문의 (1)
임정호 | 2023.11.30 | 추천 0 | 조회 24
임정호 2023.11.30 - 24
64350
New 대출금리 자동계산수식 여쭤봅니다 (2)
집중력귀신 | 2023.11.30 | 추천 0 | 조회 22
집중력귀신 2023.11.30 - 22
64340 함수/공식
New 셀 값을 변경하는 수식 사용 중에 작동이 안돼서 문의드립니다. 첨부파일 (4)
슈바츠론 | 2023.11.30 | 추천 0 | 조회 33
슈바츠론 2023.11.30 - 33
64333 함수/공식
New 실시간 데이터 연동 방법 좀 알려주실 수 있나요?? (2)
dfgkdflglkdf | 2023.11.30 | 추천 0 | 조회 45
dfgkdflglkdf 2023.11.30 - 45
64329 함수/공식
New IF와 VLOOKUP을 같이써서 처리를 해야하는 셀인데 어떻게 해야 맞는지 모르겠네요 ㅠㅠ... 첨부파일 (2) 답변완료
fkdn**** | 2023.11.29 | 추천 0 | 조회 60
fkdn**** 2023.11.29 - 60
64327 함수/공식
New 몇주째 도저히 모르겠습니다ㅜㅜ 일별/주간/월별 데이터 불러올 함수 알려주실분 꼭 부탁드립니다. 엑셀파일 (5) 답변완료
shin**** | 2023.11.29 | 추천 0 | 조회 87
shin**** 2023.11.29 - 87
64326 구글시트
New 엑셀이나 구글시트에서 특정 데이터를 입력하면 표시,금지 할수있는 방법을 알수있을까요 ? (3)
다운이남편 | 2023.11.29 | 추천 0 | 조회 41
다운이남편 2023.11.29 - 41
64325 함수/공식
New 엑셀함수관련 문의드립니다 기초적인부분이지만 해결방법이 궁금합니다 첨부파일 (3) 답변완료
gnsdlgns | 2023.11.29 | 추천 0 | 조회 50
gnsdlgns 2023.11.29 - 50
64320 파워쿼리/피벗
New 파워쿼리 엑셀 파일 불러오기 액세스 거부 ㅜㅠ 첨부파일 (1)
방글당근 | 2023.11.29 | 추천 0 | 조회 41
방글당근 2023.11.29 - 41
64317 VBA
New 집계함수를 반영하니 계산시간이 많이 걸립니다 다른방법이 없는지요 엑셀파일 (3)
슈토파이터 | 2023.11.29 | 추천 0 | 조회 63
슈토파이터 2023.11.29 - 63
64316 문서서식
New 조건부서식 하나가 막히는 데 도와주세요 첨부파일 (1)
수캄 | 2023.11.29 | 추천 0 | 조회 51
수캄 2023.11.29 - 51
64312 함수/공식
New 특정 텍스트 추출 엑셀파일첨부파일 (4)
ggplay**** | 2023.11.29 | 추천 0 | 조회 75
ggplay**** 2023.11.29 - 75
64311 함수/공식
New 함수 문의 첨부파일 (1)
비갠오후 | 2023.11.29 | 추천 0 | 조회 54
비갠오후 2023.11.29 - 54
64310 함수/공식
New 기간 중복을 제외하고 주당 사용 일수 계산 문의드립니다. 엑셀파일첨부파일 (2)
함수시르미 | 2023.11.29 | 추천 0 | 조회 30
함수시르미 2023.11.29 - 30
64294 함수/공식
New 표준편차 계산시 특정월의 데이터만 계산하고 싶습니다 엑셀파일첨부파일 (3) 답변완료
전설 | 2023.11.29 | 추천 0 | 조회 41
전설 2023.11.29 - 41