¸­°ê¬w

¸Õ¸Õ

1. Sub test()
   
2. Dim Arr, Brr(1 To 65536, 1 To 2), C, UC
   
3. Application.ScreenUpdating = False
   
4. Arr = Range("a2:b" & Cells(Rows.Count, "a").End(3).Row).Value
   
5. Range("e2", Cells(Rows.Count, "f")).ClearContents
   
6. For Each A In Range("b2:b" & Cells(Rows.Count, "b").End(3).Row).Value
   
7.     For Each b In Split(A, Chr(10))
   
8.         If Len(b) > 0 Then
   
9.         n = n + 1
   
10.         UC = UC + 1
    
11.         Brr(n, 1) = b
    
12.         End If
    
13.     Next b
    
14.         m = m + 1
    
15.         Brr(m, 2) = UC
    
16.         UC = 0
    
17. Next A
    
18. [f2].Resize(n, 1) = Brr
    
19. For j = 2 To UBound(Arr) + 1
    
20.     Cells(j, 1).Copy _
    
21.     Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Resize(Brr(j - 1, 2), 1)
    
22. Next j
    
23. Application.ScreenUpdating = True
    
24. End Sub

½Æ»s¥N½X

¸­°ê¬w

ª`ÄÀªº³¡¤À¸Ñ°£¥i¹ê²{¥h­«

1. Sub test11()
   
2. Dim Arr, Brr(1 To 100, 1 To 2), n%, rg As Range, d
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Application.ScreenUpdating = False
   
5. Arr = Range("a2:b" & Cells(Rows.Count, "a").End(3).Row).Value
   
6. Range("e2", Cells(Rows.Count, "f")).ClearContents
   
7. Set rg = Range("b2:b" & Cells(Rows.Count, "b").End(3).Row)
   
8. For Each a In rg
   
9.     For Each b In Split(a, Chr(10))
   
10. '        If Not d.exists(b) Then
    
11. '            d(b) = ""
    
12.             If Len(b) > 0 Then
    
13.             n = n + 1
    
14.             Brr(n, 1) = a.Offset(0, -1)
    
15.             Brr(n, 2) = b
    
16.             End If
    
17. '        End If
    
18.     Next b
    
19. Next a
    
20. Range("f2").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
    
21. Application.ScreenUpdating = True
    
22. End Sub

½Æ»s¥N½X