Excel - 행의 중복 열 값 강조 표시 및 제거

Question

저는 수백 개의 데이터 열이 포함된 대규모 문서를 작업하고 있습니다. 이러한 행 중 다수에는 제거해야 하는 열에 중복된 값이 있습니다.

다음은 샘플 시트입니다.

내가 필요한 것은 각 행을 탐색하고 B:E 열에서 중복 항목을 찾아 셀 중 하나를 제외하고 모두 삭제하고, 가급적이면 빈 셀을 피하기 위해 나머지 셀을 왼쪽으로 이동하는 것입니다. 모든 행과 나머지 데이터를 그대로 유지해야 합니다.

따라서 위의 예를 보면 결과는 다음과 같습니다.

몇 가지 참고사항:

• 문제의 셀은 모두 각 행의 끝에 나타납니다.
• 추론: 이 모든 값은 단일 열에 목록으로 저장되었으며 Text to Columns. 이제 정리하고 중복 항목을 제거해야 합니다.
• 수천 개의 행과 중복된 항목이 있을 수 있는 수백 개의 추가 열이 있습니다.

VBA에서도 이것이 가능합니까? 어떤 제안이라도 대단히 감사하겠습니다. 감사합니다!

Answer 1

게시된 답변(10K 행 및 1K 열)에 대한 속도 테스트 결과는 다음과 같습니다.

VBA 1 - Time:  19.488 sec - RemoveRowDupes (this answer)

VBA 2 - Time: 109.434 sec - dostuff (after turning off ScreenUpdating)

Formula test: N/A (gave up after 5 minutes filling out 10Kx1K range with array, at 9%)

---

Option Explicit
Public Sub RemoveRowDupes()
    Dim ur As Range, cc As Long, r As Range, a As Variant
    Dim s As String, i As Long, l As Long, t As Long, tt As Double, tr As String
    tt = Timer
    Set ur = Sheet1.UsedRange
    cc = ur.Columns.Count - 1
    With ur.Offset(, 1).Resize(, cc)
        Application.ScreenUpdating = False
        For Each r In .Rows
            s = Join(Application.Transpose(Application.Transpose(r)), "|")
            a = Split(s, "|"):
            l = Len(s)
            For i = 0 To cc - 1
                If Len(a(i)) > 0 Then
                    s = Replace(s, a(i), "^^")
                    s = Replace(s, "^^", a(i), , 1)
                    s = Replace(s, "^^", vbNullString)
                    If l > Len(s) Then
                        a = Split(s, "|")
                        l = Len(s)
                    End If
                End If
            Next
            s = Replace(s, "||", "|")
            If Right(s, 1) = "|" Then s = Left(s, Len(s) - 1)
            t = Len(s) - Len(Replace(s, "|", ""))
            r.ClearContents:    r.Resize(, t + 1) = Split(s, "|")
        Next
        Application.ScreenUpdating = True
    End With
    tr = "Rows: " & Format(ur.Rows.Count,"#,###") & "; Cols: " & Format(cc,"#,###") & "; "
    Debug.Print tr & "Time: " & Format(Timer - tt, "0.000") & " sec - RemoveRowDupes()"
End Sub

---

테스트 데이터:

---

결과 - RemoveRowDupes()

---

결과 - dostuff()

---

메모:이 답변은 범위와 상호 작용하는 대신 배열을 사용하여 (필요한 경우) 개선될 수 있습니다.

Answer 2

VB를 사용하여 데이터를 제자리에서 처리하려면 다음을 사용할 수 있습니다.

Sub dostuff()
Dim myarray As Variant
ReDim myarray(10000)

i = 0 'row iterator

Do While (Range("A1").Offset(i, 0).Value <> "")
 j = 0 'single item iterator
 k = 0 'column iterator
 m = 0 'stored array iterator
 m_max = 0 'number of unique values on the row

 'iterate single values
 Do While (Range("B1").Offset(i, j).Value <> "")
  temp = Range("B1").Offset(i, j).Value

  'compare to saved
  flag = 0
  m = 0
  Do While (m <= m_max)
   If temp = myarray(m) Then
     flag = 1
   End If
   m = m + 1
  Loop

  'add if unique
  If flag = 0 Then
   m_max = m_max + 1
   myarray(m_max) = temp
  End If

  j = j + 1
 Loop

 'clear existing
 Range("B1").Offset(i, 0).Select
 Range(Selection, Selection.End(xlToRight)).Clear

 'write saved
 m = 1
 Do While m <= m_max
  Range("B1").Offset(i, m - 1).Value = myarray(m)
  m = m + 1
 Loop

  i = i + 1
Loop
End Sub

Answer 3

수식을 사용하여 이를 수행할 수 있지만 올바른 값은 적어도 일시적으로 다른 위치에 있습니다. 데이터를 동일한 위치에 유지하려면 새 데이터를 복사하고 이전 데이터 위에 선택하여 붙여넣기 > 값을 입력하면 됩니다.

B7에서 오른쪽 아래로 채워진 이 배열 수식은 아래와 같은 결과를 제공합니다.

=IFERROR(INDEX($B1:$E1,,MATCH(0,COUNTIF($A7:A7,$B1:$E1),0)),"")

이는 배열 수식이므로 로 입력해야 합니다 CTRLShiftEnter.

이 공식이 어떻게 작동하는지에 대한 튜토리얼은 다음에서 제공됩니다.엑셀젯.