☰

별도의 시트에 있는 2개의 열을 하나의 열로 병합

tag-icon • tag-icon microsoft-excel worksheet-function vba
별도의 시트에 있는 2개의 열을 하나의 열로 병합

두 개의 개별 시트에 2개의 목록이 있고 이를 세 번째 시트로 결합하려고 합니다. 이러한 목록은 사용자 입력에 따라 자동으로 채워지므로 목록 내의 행 수가 다를 수 있습니다. 따라서 시트 1의 첫 번째 열에서 아무것도 감지하지 못하면 다음과 같이 시트 3의 목록을 시트 2의 열로 채우기 시작합니다.

시트 1

Make    Model     License Plate
Ford    Escape    UVC345
Honda   Civic     KD2YR9

시트 2

Make    Model    License Plate 
Dodge   Charger  34TRLS2
VW      Passat   V70YTR

시트 3

Make    Model    License Plate
Ford    Escape   UVC345
Honda   Civic    KD2YR9
Dodge   Charger  34TRLS2
VW      Passat   V70YTR

업데이트:

VBA를 사용하여 복사하여 붙여넣을 때 직면하게 되는 문제는 시트 1의 초기 목록을 자동으로 채우는 데 사용되는 수식을 붙여넣고 식별한다는 것입니다. 수식을 붙여넣기 때문에 위치를 올바르게 식별하지 못합니다. 마지막 셀은 시트 3에 있습니다.

Sub Copy_Alternatives()

Worksheets("CNA eTool Alternatives").Range("A:A").Copy
Worksheets("Repair Replacement Recom").Range("A:A").PasteSpecial xlPasteValues
Worksheets("CNA eTool Alternatives").Range("B:B").Copy
Worksheets("Repair Replacement Recom").Range("B:B").PasteSpecial xlPasteValues
Worksheets("CNA eTool Alternatives").Range("C:C").Copy
Worksheets("Repair Replacement Recom").Range("C:C").PasteSpecial xlPasteValues

End Sub

Sub Copy_Paste_Range()

Dim lNewRow As Long
Dim lDataRow As Long

ThisWorkbook.Activate

lNewRow = Worksheets("CNA eTool Addit. Alternatives").Cells(Worksheets("CNA eTool Addit. Alternatives").Rows.Count, "H").End(xlUp).Row

lDataRow = Worksheets("Repair Replacement Recom").Cells(Worksheets("Repair Replacement Recom").Rows.Count, 1).End(xlUp).Row
lDataRow = lDataRow + 1

Worksheets("CNA eTool Addit. Alternatives").Range("H2:J" & lNewRow).Copy
Worksheets("Repair Replacement Recom").Range("A" & lDataRow).PasteSpecial

End Sub

답변1

워크시트 기능으로 만들 수 있습니다.

첫 번째 시트는 Sheet1이고 두 번째 시트는Extra1

A2의 병합된 시트에 다음 수식을 입력한 다음 복사하여 다른 셀에 붙여넣습니다.

=IF(ROW()>COUNTA(Sheet1!A:A)+COUNTA(Extra1!A:A)-1,"",INDIRECT(IF(ROW()<=COUNTA(Sheet1!A:A),"Sheet1!","Extra1!")&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&ROW()-IF(ROW()<=COUNTA(Sheet1!A:A),0,COUNTA(Sheet1!A:A)-1)))

작동 방식:

  1. 현재 줄이 첫 번째 시트의 줄 수 + 두 번째 시트에서 1을 뺀 값(두 시트 모두 헤더가 있기 때문에)보다 높은지 확인합니다.
  2. 현재 줄이 더 크면 비어 있음을 표시합니다. 그렇지 않으면 다음 단계로 이동합니다.
  3. 를 준비합니다 INDIRECT. 현재 줄이 첫 번째 시트의 줄보다 낮으면 첫 번째 시트 이름을 가져옵니다. 그렇지 않으면 두 번째 시트 이름을 얻습니다.
  4. 이 트릭 을 사용하여 SUBSTITUTE + ADDRESS열의 현재 문자를 반환합니다. 그러면 모든 필드에 동일한 수식을 사용하고 열이 AA가 되어도 허용되므로 좋습니다.
  5. 마지막으로, 현재 줄이 첫 번째 시트의 항목 수보다 낮으면 현재 줄을 가져오고, 그렇지 않으면 현재 줄에서 첫 번째 시트의 줄 수를 뺀 값에서 1을 뺀 값을 가져와 두 번째 시트의 시작 줄을 반환합니다. .

읽기가 다소 부담스럽고 ​​어렵다는 것을 알고 있지만 때로는 수식이 VBA보다 낫습니다.

편집: Welp, 저는 깨달음을 얻었고 작동하는 VBA 버전을 얻었습니다.

Sub merge()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = Sheets("Sheet1") 'First sheet to get merged
Set ws2 = Sheets("Extra1") 'Second sheet to get merged
Set ws3 = Sheets("Merged") 'Name of the sheet you want results
Dim lr As Long 'To return the number of the last row
Dim arrayone As Variant
lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
arrayone = Range(ws1.Cells(2, 1), ws1.Cells(lr, 3)).Value 'From Row 2 onwards, considering it has headers
Dim arr2 As Variant
lr = ws2.Cells(Rows.Count, 1).End(xlUp).Row
arr2 = Range(ws2.Cells(2, 1), ws2.Cells(lr, 3)).Value 'From Row 2 onwards, considering it has headers
Dim arr3 As Variant
ReDim arr3(1 To UBound(arrayone, 1) + UBound(arr2, 1), 1 To 3)
For i = 1 To UBound(arr3, 1)
    For j = 1 To 3
        If i <= UBound(arrayone, 1) Then
            arr3(i, j) = arrayone(i, j)
        Else
            arr3(i, j) = arr2(i - UBound(arrayone, 1), j)
        End If
    Next j
Next i
Range(ws3.Cells(2, 1), ws3.Cells(UBound(arr3) + 1, 3)).Value = arr3
End Sub

관련 정보