[VBA] 검색되는 단어, 검색되지 않는 단어 찾기

지식인에서 들어온 질문.

리스트상의 단어가 데이터상의 단어를 포함한 긴 단어에 있을 경우 해당하는 모든 중복되는 데이터에 대해 Listed 밑에 리스상의 단어로 하나만 표기하면 됩니다.
가령 APPLE 이 리스트 단어이면 DATA상에 APPLE_1/APPPL_2가 있으면 그냥 APPLE 만 표기하도록 매크로를 작성해주세요.

List에 없는 단어 중 Name에 있으면 Non-listed에 표시해주세요

여기서는 안보이지만, Apple, Melon, Strawberry, Pear 등으로 이루어진 List목록이 따로 있다

Potato는 List목록에는 없는데 Name에는 있기때문에 Non-listed에 표시된다.

사실 앞의 문제는 해결이 쉽다. .Find 메서드를 이용하면 된다.

시트에서 Ctrl-F 를 누른 것과 같은 기능을 한다. 

c = Worksheets(1).Range("a1:a500").Find(2, lookin:=xlValues) 

위는 a1:a500에서 2라는 값을 찾아서 셀을 반환한다.

즉, 여기서  C 는 Range 변수로 정의되어야한다.

그런데 두번째 문제가 좀 어려웠다. 

List에 있는 각 단어를 가지고 Name에 있는 셀을 다 찾은 다음, Name에 있는 단어를 하나씩 이용해서 List를 뒤져야하나?

그러면 시간이 오래 걸릴 것 같아서 기각이다.

마침내 방법을 생각해 냈는데, 배열변수를 이용하는 것이다!

배열변수의 크기를 Name의 단어 갯수만큼 설정하고

Find 및 Findnext를 이용해서 List에 있는 단어를 계속 찾고,, 찾을 때마다 해당 셀의 위치(행번호)를 배열변수의 위치에다 매칭시킨다

예를 들어, Name에 100개의 단어가 A1셀부터 A100셀까지 들어있으면  

arr라는 배열변수를 arr(1)부터 arr(100)까지 설정한다. 

이제 List에 있는 첫번째 단어를 Name에서 찾으니, A3, A15에서 발견되었다고 치자

그럼 arr(3)과 arr(15)에 1을 넣는다

이제 List에 있는 두번째 단어를 Name에서 찾으니, A6, A50, A99에서 발견되었다고 치자

그럼 arr(6), arr(50), arr(99)에 1을 넣는다.

이런 식으로 List에 있는 모든 단어에 대한 검색이 끝나면, 

arr()의 배열변수 중 arr(3), arr(6), arr(15), arr(50), arr(99)에는 1이 들어가 있다. 

여기서 3,6,15,99의 의미는 Name의 셀주소 A3, A6, A15, A50, A99에 있는 단어들이 List에 있는 단어라는 의미다. 

반대로, 나머지 0이 들어가 있는 배열변수의 위치는 List에 있는 단어가 하나도 발견되지 않은 위치이다

이제 arr()변수를 하나씩 조사하면서 0값이 있는 위치를 골라내고

그 숫자(위치)에 해당하는 Name의 행에 있는 셀에 있는 단어를 찾아내면 된다. 

이제 배열변수에 대해  알 것 같다.

단어찾기.xlsm

Sub example_6()

 

Dim c As Range

Dim intsize As Integer

Dim s As String

Dim intr As Integer

Dim wd2find As String

Dim StrFirstaddr As String

Dim StrAddr As String

Dim arrayExist() As Variant

Dim rngName As Range

Dim rngList As Range

Dim rngOutput As Range

Dim rngNolist As Range

 

Set rngList = Sheet1.Range("a1").CurrentRegion         

Set rngName = Sheet1.Range("c1").CurrentRegion

Set rngOutput = Sheet1.Range("e1").CurrentRegion

Set rngNolist = Sheet1.Range("g1").CurrentRegion

 

intsize = rngName.Count - 1

 

ReDim arrayExist(2 To intsize + 1)

 

For i = 2 To rngList.Count

wd2find = Cells(i, 1).Value

‘list에 있는 단어들을 찾는 단어를 나타내는 변수 wd2find에 배당

Set c = rngName.Find(wd2find, lookat:=xlPart)

‘여기서 c가 문자열이 아니라 범위Range라는 점이 중요

 

If Not c Is Nothing Then

StrFirstaddr = c.Address 

 ‘무한 루프로 찾지 않도록 처음 찾은 주소 변수 배당

StrAddr = c.Address 

 ‘두 번째부터 찾은 주소

Do

intr = c.Row

arrayExist(intr) = 1 

 ‘이 부분은 arrayExist(c.row) = 1 이렇게 줄일 수 있을 듯 ’찾는 단어가 존재하면 존재하는 셀의 열 번호와 같은 arrayExist 배열의 열 번호에 1을 입력한다.

Set c = rngName.FindNext(c)

StrAddr = c.Address

Loop While Not c Is Nothing And StrAddr <> StrFirstaddr

‘찾은 주소가 중복되지 않는 한 반복

 

Set c = rngOutput.Find(wd2find, lookat:=xlWhole)

If c Is Nothing Then

rngOutput.Cells(Rows.Count, 1).End(3)(2).Value = wd2find

‘해당 단어가 output 리스트에 중복인지 확인.

‘중복이 아니면 output 리스트에 추가

End If

End If

Next

 

‘여기부터는 list에 없는 단어 나열하기

For i = 2 To intsize + 1

If arrayExist(i) = 0 Then

‘배열 중 0값. 즉, 한번도 검색되지 않은 단어가 있는 배열의 위치를 찾는다

s = Cells(i, 3).Value

‘ 그 위치와 같은 위치에 있는 name 범위의 단어를 가져온다. 여기서 Cells(i, 3)부분은 세 번째 열, 즉 C열을 의미한다. C열의 2번째 행인 C2부터 시작해서 아래로 있는 단어들 중에서 arrayExist의 배열상 0값이 있는 위치와 같은 위치의 값을 가져온다

Set c = rngNolist.Find(s, lookat:=xlWhole)

If c Is Nothing Then

'검색되지 않은 단어가 nolist목록에 이미 존재하는지 검사

rngNolist.Cells(Rows.Count, 1).End(3)(2).Value = s

End If

‘해당 단어가 nolist 목록에 있는지 검사하고, 없으면 추가

End If

Next i

 

End Sub