다른 열에 있는 동일한 값의 각 집합에 대해 열에 있는 고유한 값을 어떻게 얻을 수 있습니까?

Question

다음 두 개의 열이 있습니다.

Con.By  Prod
   A     1
   A     1
   A     2
   A     2
   B     1
   B     1
   B     2
   B     2

Con.By열 이나 열 에서 고유한 값을 쉽게 얻을 수 있습니다 Prod. 그러나 내 요구 사항은 Prod열의 각 고유 값에 대해 Column의 고유 값을 얻는 것입니다 Con.By. 따라서 위의 데이터 열에 대해 내가 의도한 출력은 다음과 같습니다.

Con.By  Prod
   A     1
         2
   B     1
         2

어디서부터 시작해야 할지조차 모르겠습니다. Con.By각 값에 대한 열을 개별적으로 필터링한 다음 열에서 고유한 값을 찾기 위해 VBA 코드를 작성해 보았습니다 Prod. 그러나 내 Con.By열에 너무 많은 항목이 포함되어 있고 그 중 일부는 시간이 지남에 따라 변경될 수 있으므로 이 방법은 작동하지 않습니다 .

필요한 결과를 얻는 가장 좋은 방법은 무엇입니까? Excel 수식이 있습니까? 아니면 VBA 코딩이 필요합니까?

Answer 1

이 방법을 시도해 볼 수 있습니다. 두 번째 열의 고유 항목 수집을 돕기 위해 사용자 정의 클래스를 사용합니다.

일반 모듈과 클래스 모듈의 코드는 기존 멤버와 동일한 키를 가진 컬렉션에 멤버를 추가하려고 하면 오류가 457생성된다는 사실을 활용합니다.

소스(Src) 및 결과(Res)에 대한 워크시트 및 범위의 차이를 설명하기 위해 변경해야 하는 위치를 코드에서 확인할 수 있습니다.

클래스 모듈의 이름을 바꿔야 합니다 cConBy. 당신 후 Insert Class Module,F4속성창을 엽니다. Name거기서 매개변수를 변경하세요 .

수업 모듈

---

Option Explicit
Private pConBy As String
Private pProd As String
Private pProds As Collection

Private Sub Class_Initialize()
    Set pProds = New Collection
End Sub

Public Property Get ConBy() As String
    ConBy = pConBy
End Property
Public Property Let ConBy(Value As String)
    pConBy = Value
End Property

Public Property Get Prod() As String
    Prod = pProd
End Property
Public Property Let Prod(Value As String)
    pProd = Value
End Property

Public Function AddProd(Value As String)
    On Error Resume Next
    pProds.Add Value, CStr(Value)
    On Error GoTo 0
End Function

Public Property Get Prods() As Collection
    Set Prods = pProds
End Property

---

일반 모듈

---

Option Explicit
Sub UniqueConBy()
    Dim cCB As cConBy, colCB As Collection
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes() As Variant
    Dim I As Long, J As Long, K As Long
    Dim lRowCount As Long

'Source and results location
Set wsSrc = Worksheets("Sheet1")
Set wsRes = Worksheets("Sheet1")
    Set rRes = wsRes.Cells(1, 5)
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=2)
End With

'Collect and consolidate the data
Set colCB = New Collection
On Error Resume Next
For I = 2 To UBound(vSrc, 1)
    Set cCB = New cConBy
    With cCB
        .ConBy = vSrc(I, 1)
        .Prod = vSrc(I, 2)
        .AddProd .Prod
        lRowCount = lRowCount + 1
        colCB.Add cCB, CStr(.ConBy)
        Select Case Err.Number
            Case 457
                With colCB(CStr(.ConBy))
                    lRowCount = lRowCount - .Prods.Count - 1
                    .AddProd cCB.Prod
                    lRowCount = lRowCount + .Prods.Count
                End With
                Err.Clear
            Case Is <> 0
                MsgBox "Error: " & Err.Number & vbTab & Err.Description
                Stop
        End Select
    End With
Next I
On Error GoTo 0

'Create results array
ReDim vRes(0 To lRowCount, 1 To 2)

'column labels
For I = 1 To UBound(vRes, 2)
    vRes(0, I) = vSrc(1, I)
Next I

'populate the array
For I = 1 To colCB.Count
    With colCB(I)
        K = K + 1
        vRes(K, 1) = .ConBy
        vRes(K, 2) = .Prods(1)
        For J = 2 To .Prods.Count
            K = K + 1
            vRes(K, 2) = .Prods(J)
        Next J
    End With
Next I

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

---

편집하다:

원하는 것과 비슷하지만 약간 다른 출력을 제공하는 대체 방법은 간단히 데이터 리본/데이터 도구 탭에서 중복 항목 제거 옵션을 사용하는 것입니다. A열과 B열을 모두 선택합니다.

이 방법을 적용하기 전에 데이터가 정렬되었는지 확인하십시오. VBA 방법을 사용하면 정렬이 필요하지 않습니다.

게시된 데이터를 사용하면 결과는 다음과 같습니다.

조건부 서식을 사용하여 A열의 중복 항목을 제거할 수 있습니다. 예: =$A2=$A1 수식을 사용하고 텍스트 색상의 서식을 배경과 동일하게 지정합니다. Con.By 값은 여전히 ​​존재하지만 표시되지는 않습니다.

Answer 2

이해하기 쉽지만 Ron의 답변만큼 자동화 친화적이지는 않을 것 같은 다음 레시피를 시도해 보세요.

1. Con.By가 열 A에 있고 열 B에 있다고 가정하고 Prod다른 열(예: C)에 "_"와 같은 구분 기호를 사용하여 두 열을 연결합니다.

   =A2&"_"&B2이는 다음과 같습니다=CONCATENATE(A2,"_",B2)

2. 예제를 사용하면 출력은 다음 A_1과 같습니다. C 열을 Paste ValuesD 열에만 복사합니다.

3. D열을 강조 표시하고 리본 메뉴를 사용하여 를 선택합니다 Data -> Remove Duplicates. D 열은 다음과 같습니다. A_1 A_2

4. 데이터를 두 개의 별도 열로 다시 분할하려면 리본 메뉴를 사용하고 를 선택합니다 Data -> Text to Columns. '구분됨'을 선택하세요.

Answer 3

이해하기 쉽지만 Ron의 답변만큼 자동화 친화적이지는 않을 것 같은 다음 레시피를 시도해 보세요.

1. Con.By가 열 A에 있고 열 B에 있다고 가정하고 Prod다른 열(예: C)에 "_"와 같은 구분 기호를 사용하여 두 열을 연결합니다.

   =A2&"_"&B2이는 다음과 같습니다=CONCATENATE(A2,"_",B2)

2. 예제를 사용하면 출력은 A_1다음과 같습니다. C 열을 Paste ValuesD 열에만 복사합니다.

3. D열을 강조 표시하고 리본 메뉴를 사용하여 를 선택합니다 Data -> Remove Duplicates. D 열은 다음과 같습니다. A_1 A_2

4. 데이터를 두 개의 별도 열로 다시 분할하려면 리본 메뉴를 사용하고 를 선택합니다 Data -> Text to Columns. Delimited첫 번째 옵션을 선택하고 두 번째 옵션을 선택합니다 Other. _이 경우 구분 기호가 있습니다 .

이렇게 하면 원하는 것에 가까운 결과를 얻을 수 있습니다.