Bagaimana cara mengubah urutan sel dalam satu kolom berdasarkan nilai unik di kolom lain?

Misalkan, Anda memiliki rentang data yang berisi dua kolom, sekarang, Anda ingin mengubah urutan sel dalam satu kolom menjadi baris horizontal berdasarkan nilai unik di kolom lain untuk mendapatkan hasil berikut. Apakah Anda punya ide bagus untuk mengatasi masalah ini di Excel?

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan rumus

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan kode VBA

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan Kutools for Excel

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan rumus

Dengan rumus array berikut, Anda dapat mengekstrak nilai unik dan mengubah urutan data yang sesuai menjadi baris horizontal, lakukan hal berikut:

1. Masukkan rumus array ini: = INDEKS ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) ke dalam sel kosong, D2, misalnya, dan tekan Shift + Ctrl + Masuk kunci bersama untuk mendapatkan hasil yang benar, lihat tangkapan layar:

Note: Dalam rumus di atas, A2: A16 adalah kolom tempat Anda ingin mencantumkan nilai unik, dan D1 adalah sel di atas sel formula ini.

2. Kemudian seret gagang isian ke sel untuk mengekstrak semua nilai unik, lihat tangkapan layar:

3. Dan kemudian masukkan rumus ini ke dalam sel E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), dan ingat untuk menekan Shift + Ctrl + Masuk kunci untuk mendapatkan hasilnya, lihat tangkapan layar:

Note: Dalam rumus di atas: B2: B16 adalah data kolom yang ingin Anda ubah urutannya, A2: A16 adalah kolom tempat Anda ingin mengubah urutan nilai berdasarkan, dan D2 berisi nilai unik yang telah Anda ekstrak pada Langkah 1.

4. Kemudian seret gagang isian ke kanan sel yang ingin Anda daftar data yang dialihkan sampai menampilkan 0, lihat tangkapan layar:

5. Dan kemudian lanjutkan menyeret gagang isian ke kisaran sel untuk mendapatkan data yang dialihkan seperti gambar berikut yang ditunjukkan:

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan kode VBA

Mungkin rumusnya rumit untuk Anda pahami, di sini, Anda dapat menjalankan kode VBA berikut untuk mendapatkan hasil yang diinginkan.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.

Kode VBA: Mengubah urutan sel dalam satu kolom berdasarkan nilai unik di kolom lain:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Lalu tekan F5 kunci untuk menjalankan kode ini, dan kotak prompt akan muncul untuk mengingatkan Anda memilih rentang data yang ingin Anda gunakan, lihat tangkapan layar:

4. Dan kemudian klik OK tombol, kotak prompt lain akan muncul untuk mengingatkan Anda untuk memilih sel untuk meletakkan hasilnya, lihat tangkapan layar:

6. Klik OK tombol, dan data di kolom B telah dialihkan berdasarkan nilai unik di kolom A, lihat tangkapan layar:

Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan Kutools for Excel

Jika Anda memiliki Kutools untuk Excel, menggabungkan Lanjutan Gabungkan Baris dan Pisahkan Sel utilitas, Anda dapat dengan cepat menyelesaikan tugas ini tanpa rumus atau kode apa pun.

Setelah menginstal Kutools untuk Excel, lakukan hal berikut:

1. Pilih rentang data yang ingin Anda gunakan. (Jika Anda ingin menyimpan data asli, silakan salin dan tempel data ke lokasi lain terlebih dahulu.)

2. Lalu klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris, lihat tangkapan layar:

3. di Gabungkan Baris Berdasarkan Kolom kotak dialog, lakukan operasi berikut:

(1.) Klik nama kolom yang datanya ingin Anda ubah, lalu pilih Kunci utama;

(2.) Klik kolom lain yang ingin Anda ubah urutannya, dan klik Menggabungkan lalu pilih satu pemisah untuk memisahkan data gabungan, seperti spasi, koma, titik koma.

4. Lalu klik Ok tombol, data di kolom B telah digabungkan menjadi satu sel berdasarkan kolom A, lihat tangkapan layar:

5. Dan kemudian pilih sel gabungan, dan klik Kutools > Gabungkan & Pisahkan > Pisahkan Sel, lihat tangkapan layar:

6. di Pisahkan Sel kotak dialog, pilih Pisahkan ke Kolom bawah Tipe pilihan, dan kemudian pilih pemisah yang memisahkan data gabungan Anda, lihat tangkapan layar:

7. Lalu klik Ok tombol, dan pilih sel untuk meletakkan hasil pemisahan di kotak dialog yang muncul, lihat tangkapan layar:

8. Klik OK, dan Anda akan mendapatkan hasil yang Anda butuhkan. Lihat tangkapan layar:

Unduh dan uji coba gratis Kutools untuk Excel Sekarang!

Demo: Ubah urutan sel dalam satu kolom berdasarkan nilai unik dengan Kutools for Excel