How to Transpose Duplicate Rows to Columns in Excel (4 Ways)

If you are looking for some of the easiest and most effective ways to transpose duplicate rows to columns in Excel, then you are in the right place. So, let’s dive into the main article to know the details of the procedures to transpose duplicate rows quickly.

Download Workbook


4 Ways to Transpose Duplicate Rows to Columns in Excel

Here, we have the following dataset containing the sales records of different products, and notice that we have some repeated rows also. Using the following methods we will try to convert the duplicate rows into columns easily.

transpose duplicate rows to columns in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using Conditional Formatting to Transpose Duplicate Rows to Columns

Here, we will use Conditional Formatting to highlight the duplicate rows, so we will be able to transpose the duplicate rows easily after that in the Column1 and Column2 columns.

transpose duplicate rows to columns in Excel

Steps:
➤ Select the data range and then go to the Home Tab >> Styles Group >> Conditional Formatting Dropdown >> Highlight Cells Rules Option >> Duplicate Values Option.

Conditional Formatting

After that, the Duplicate Values dialog box will appear.
➤ Choose the Duplicate and Light Red Fill with Dark Red Text options in the Format cells that contain box.
➤ Press OK.

transpose duplicate rows to columns in Excel

Then, the duplicate rows for the products Walnut and Apple will be highlighted, so we can easily convert the first rows of each duplicate to columns.

Conditional Formatting

➤ Select the Rows for the products Walnut and Apple by pressing CTRL while selecting them and to copy them press CTRL+C.

Conditional Formatting

➤ To paste the rows as columns Right-Click on the destination cell and then select the Transpose option from various Paste Options.

Conditional Formatting

Finally, you will be able to transpose the duplicate rows to columns.

transpose duplicate rows to columns in Excel

Read More: Conditional Transpose in Excel (2 Examples)


Method-2: Using COUNTIF Function

In this section, we will combine the names of the salespersons and sales values with the Ampersand operator in the Extra1 column, and then we will count the number of repetitions of the rows by using the COUNTIF function in the Extra2 column. Finally, we will convert the repeated rows to columns using the Transpose option.

transpose duplicate rows to columns in Excel

Steps:
➤ Type the following formula in cell D4 to combine all of the cells of each row.

 =B4&C4

Here, B4 is the name of the SalesPerson, C4 is the Sales value, and & will join them.

COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTIF Function

Then, you will get the combination of the cells of each row in the Extra1 column.

transpose duplicate rows to columns in Excel

➤ To count the repetitions of the rows of the Extra1 column write the following formula in cell E4.

=COUNTIF($D$4:D4,D4)

Here, D4 is the criteria and $D$4:D4 is the range and this range will be from the starting row to the row up to which the formula is used, such as for Row 8 the range will be $D$4:D8.

COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTIF Function

After that, we will have the number of repetitions in the Extra2 column. Here, 2 for Lara and Howard means they appeared twice with their sales values in this dataset and so now we will convert these rows to columns.

transpose duplicate rows to columns in Excel

➤ Select the Rows for Lara and Howard by pressing CTRL while selecting them and to copy them press CTRL+C.

COUNTIF Function

➤ To paste the rows as columns Right-Click on the destination cell and then select the Transpose option from various Paste Options.

COUNTIF Function

Eventually, we will be able to transpose the duplicate rows for Lara and Howard as columns.

transpose duplicate rows to columns in Excel

Read More: How to Transpose Rows to Columns in Excel (5 Useful Methods)


Similar Readings


Method-3: Using Combination of IF and COUNTIFS Functions

Here, we are going to use the IF and COUNTIFS functions to detect whether the row is duplicate or not. By doing this we can easily identify the duplicate rows and then convert them to columns.

transpose duplicate rows to columns in Excel

Steps:
➤ Type the following formula in cell E4.

=IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, "Repeated Row", "")

Here, $B$4:$B$13, $C$4:$C$13, and $D$4:$D$13 are the first, second, and third ranges. $B4, $C4, and $D4 are the first, second, and third criteria.

  • COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4) → returns a number that represents the number of repetitions of the rows.
    Output → 2
  • COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1 becomes
    2>1 → returns TRUE for fulfilling the condition otherwise FALSE.
    Output → TRUE
  • IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, “Repeated Row”, “”) becomes
    IF(TRUE, “Repeated Row”, “”) → returns the string “Repeated Row” for TRUE, otherwise a blank.
    Output → Repeated Row

IF & COUNTIFS Function

➤ Press ENTER and drag down the Fill Handle tool.

transpose duplicate rows to columns in Excel

As a result, we are getting the text string Repeated Row for the duplicate rows.

IF & COUNTIFS Function

➤ Select the Rows for the products Walnut and Apple by pressing CTRL while selecting them and to copy them press CTRL+C.

IF & COUNTIFS Function

➤ To paste the rows as columns Right-Click on the destination cell and then select the Transpose option from various Paste Options.

IF & COUNTIFS Function

Ultimately, we are getting the transposed duplicate rows like the following figure.

transpose duplicate rows to columns in Excel

Read More: How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)


Method-4: Using VBA Code to Transpose Duplicate Rows to Columns in Excel

In this section, we are going to use a VBA code to transpose the duplicate rows for the products Walnut and Apple into columns.

transpose duplicate rows to columns in Excel

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

transpose duplicate rows to columns in Excel

➤ Write the following code

Sub transform_same_rows()
Dim sht As Worksheet
Dim item, extra_column As Double
Dim storage_object As Object
Set storage_object = CreateObject("scripting.dictionary")
Set sht = Worksheets("VBA")
Total_column = 3
For increment = 4 To 13
item = Join(Application.Transpose(Application.Transpose( _
sht.Cells(increment, 2).Resize(1, Total_column))), "*")
If Not storage_object.exists(item) Then storage_object.Add item, New Collection
storage_object(item).Add sht.Cells(increment, Total_column + 1).Value
sht.Rows(increment).ClearContents
Next increment
increment = 4
For Each item In storage_object
sht.Cells(increment, 2).Resize(1, Total_column).Value = Split(item, "*")
extra_column = Total_column + 1
For Each str_value In storage_object(item)
sht.Cells(increment, extra_column) = str_value
extra_column = extra_column + 1
Next str_value
increment = increment + 1
Next item
For increment = 4 To 13
If sht.Cells(increment, 5).Value = sht.Cells(increment, 4).Value Then
sht.Range(Cells(increment, 2), Cells(increment, 4)).Copy
sht.Cells(increment, 6).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True
End If
Next increment
End Sub

Here, we have declared sht as Worksheet, item, extra_column as Double, storage_object as Object, and then set sht to the worksheet VBA and assigned Total_column to 3.
storage_object is set to the Dictionary object which will help us to store items here.
Then, we have used the FOR loop for a range of increments from row 4 to 13, and then using the TRANSPOSE function twice and then the JOIN function we have combined all of the cells of the rows with a separator * to the variable item. After that, we added the items to the storage_object and then we cleared all of the rows.
Then, the unique rows will be entered into the three columns by using the SPLIT function and then the sales values for the duplicate rows will be added to the adjacent column of the Sales column.
Then using the IF statement we will check the sales values to the values of the adjacent cells and for fulfilling the condition we will copy that corresponding row and transpose this row then.

VBA Code

➤ Press F5.
Then, we will be able to clean the duplicate rows from the dataset along with transposing the duplicate rows.

transpose duplicate rows to columns in Excel

Read More: How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to transpose duplicate rows to columns in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Further Readings

 

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo