Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to transpose duplicate rows to columns in Excel?

Supposing you have a range of data in Excel, now, you would like to transpose the duplicate rows to multiple columns as following screenshot shown, do you have any good ideas to solve this task?

Transpose duplicate rows to columns with VBA code

doc-convert-duplicate-rows-columns-1

Transform one column or row to a range or vice versa:
With Kutools for Excel’s Transform Range feature, you can quickly convert a range of cells to a single row or column, you can also convert a single row or column to a range of cells. Read more about this feature...
Go to Download
Free Trial 60 daysPurchase
PayPal / MyCommerce
doc transpose range

arrow blue right bubble Transpose duplicate rows to columns with VBA code

Unfortunately, there is no direct way for you to deal with it in Excel, but you can create a VBA code to solve it, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Transpose duplicate rows to multiple columns

Sub ConvertTable()
'Update 20150113
Dim xArr1 As Variant
Dim xArr2 As Variant
Dim InputRng As Range, OutRng As Range
Dim xRows As Long
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
xArr1 = InputRng.Value
t = UBound(xArr1, 2): xRows = 1
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(xArr1, 1)
        If Not .exists(xArr1(i, 1)) Then
            xRows = xRows + 1: .Item(xArr1(i, 1)) = VBA.Array(xRows, t)
            For ii = 1 To t
                xArr1(xRows, ii) = xArr1(i, ii)
            Next
        Else
            xArr2 = .Item(xArr1(i, 1))
            If UBound(xArr1, 2) < xArr2(1) + t - 1 Then
                ReDim Preserve xArr1(1 To UBound(xArr1, 1), 1 To xArr2(1) + t - 1)
                For ii = 2 To t
                    xArr1(1, xArr2(1) + ii - 1) = xArr1(1, ii)
                Next
            End If
            For ii = 2 To t
                xArr1(xArr2(0), xArr2(1) + ii - 1) = xArr1(i, ii)
            Next
            xArr2(1) = xArr2(1) + t - 1: .Item(xArr1(i, 1)) = xArr2
        End If
    Next
End With
OutRng.Resize(xRows, UBound(xArr1, 2)).Value = xArr1
End Sub

3. Then press F5 key to run this code, select the data range that you want to convert the duplicate rows to multiple columns in the popped out dialog, see screenshot:

doc-convert-duplicate-rows-columns-2

4. Click OK, and select one cell where you want to put the result in the following dialog box, see screenshot:

doc-convert-duplicate-rows-columns-3

5. And then click OK button, your selected data has been converted to follows:

doc-convert-duplicate-rows-columns-4

Related articles:

How to transpose / convert columns and rows into single row?

How to transpose / convert columns and rows into single column?

How to transpose / convert a single column to multiple columns in Excel?

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    arielle · 5 months ago
    Hello! Can anyone help me...

    I found this code to turn rows of data with a unique ID into a single row per ID with multiple columns (min column 5 - including ID, maximum columns 25 - including ID). Then it deletes all of the duplicate rows based on unique ID. This code sort of works, the only problem is it isn't transferring two columns of data.

    The number of rows of data per unique ID varies from 1 to 6 (therefore I would need minimum 5 columns to maximum 25 columns)

    There are 20,000 rows of data but I can break the data up by department for a minimum of 5,000 rows of data and run each department separately.

    Thanks for your help!


    My data looks something like this

    A B C D E
    ID DESCRIPTION STATE # DATE
    3 CPR US 567 6/19/2019
    3 AET US 568 6/19/2019
    4 CPR US 6/19/2019
    4 AET
    4 AED

    etc.

    I want it to look like this

    A B C D E F G H I J K L M ETC.....
    ID DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE
    3 CPR US 567 6/19/2019 AET US 568 6/19/2019
    4 CPR US AET US AED US

    Here is the code I found that sort of works (probably for what it was written for, it carries over only the data in columns D and E and omits column B & C... leaving two blank columns per data set. I like that it deletes the duplicates after moving all of the data to a single column based on unique ID

    Sub Addresses_To_Columns()

    Dim lastRow As Long
    Dim addressCount As Integer: addressCount = 0

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastRow To 3 Step -1
    If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
    addressCount = addressCount + 1
    Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 5) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 5) + 4)).Value
    Rows(i).Delete
    Else
    addressCount = 0
    End If
    Next i

    End Sub
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    This works fine for me with different number of duplicates Brian. I only had a problem with the first duplicate showing twice on my output, but that was very minor. I only wish I knew how to make it copy the duplicate results into a comma delimited format instead of new columns for each one.
  • To post as a guest, your comment is unpublished.
    Brian · 1 years ago
    Only works if there's the same number of duplicates and call him a. It doesn't work at if have different numbers of duplicates.
  • To post as a guest, your comment is unpublished.
    Alex · 3 years ago
    Really great. Saved me a lot of time
  • To post as a guest, your comment is unpublished.
    Jon · 3 years ago
    I am so incredibly happy this worked. You are my hero!!!!
  • To post as a guest, your comment is unpublished.
    Santosh · 4 years ago
    Awesome Example - saved me a lot of time.
    Thank You so much !!!
English English
en Englishar العربيةzh-CN 简体中文zh-TW 繁體中文nl Nederlandsfr Françaisde Deutschit Italianoja 日本語ko 한국어pt Portuguêsru Русскийes Español