Skip to main content

How to transpose duplicate rows to columns in Excel?

Author: Xiaoyang Last Modified: 2020-07-17

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


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()
'Updateby Extendoffice
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?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!

 

Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello everyone

I have tried this code and it worked successfully.

My question is How could i do the same using Ms Access for bigger data could i get a query works the same or not ?

Thank you so much .
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Really great. Saved me a lot of time
This comment was minimized by the moderator on the site
I am so incredibly happy this worked. You are my hero!!!!
This comment was minimized by the moderator on the site
Awesome Example - saved me a lot of time. Thank You so much !!!
This comment was minimized by the moderator on the site
Thanks a lot!

God Bless you.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations