将行转换为列 [英] Transpose the rows into columns

问题描述

我正在尝试将行数据转置为excel中的列。请查看示例下来

i有这样的数据



OrderNo客户手机项目项目价格

1 abc 000 xyz xyz item 90

1 abc 000 abc abc item 80

2 xyz 111 abc abc item 80

3 pqr 222 xyz xyz item 90

3 pqr 222 abc abc item 80

3 pqr 222 pqr pqr item 70



i想要制作如下所示



OrderNo客户手机产品ItemDesc价格Item2 ItemDesc2价格2 Item3 ItemDesc3价格

1 abc 000 xyz xyz item 90 abc abc item 80

2 xyz 111 abc abc item 90

3 pqr 222 xyz xyz item 90 abc abc item 80 pqr pqr item 70

---- -------------------------------------------------- --------------------------------

i有560行，最大重复是5行，我尝试在Excel中的转置功能，但没有运气，它没有给我结果我想要的。请帮我解决这个问题。

i am trying to transpose the rows data to columns in excel. please check the example down
i have the data like this

OrderNo Customer Mobile Item Item Des Price
1 abc 000 xyz xyz item 90
1 abc 000 abc abc item 80
2 xyz 111 abc abc item 80
3 pqr 222 xyz xyz item 90
3 pqr 222 abc abc item 80
3 pqr 222 pqr pqr item 70

i want to make it like below

OrderNo Customer Mobile Item ItemDesc Price Item2 ItemDesc2 Price2 Item3 ItemDesc3 Price
1 abc 000 xyz xyz item 90 abc abc item 80
2 xyz 111 abc abc item 90
3 pqr 222 xyz xyz item 90 abc abc item 80 pqr pqr item 70
--------------------------------------------------------------------------------------
i have 560 rows,maximum duplication is 5 rows and i tried transpose function in excel but no luck its not given me the result what i want to. please help me to solve this issue.

推荐答案

请尝试以下....这是有用的....

Please try the below....this is working....

Sub Macro1()
    Dim prevRow As Integer
    Dim currentRow As Integer
    Dim strTemp As String
    Dim pasteColumnIdx As Integer
    
    prevRow = 2 '1st row is for column heading
    currentRow = prevRow + 1 'starts from row 3
    pasteColumnIdx = 8
    
    Do While Cells(currentRow, 1).Text <> ""
    
        If Cells(prevRow, 1).Text = Cells(currentRow, 1).Text Then
            Range(Cells(currentRow, 4), Cells(currentRow, 7)).Select
            Selection.Cut
            Range(Cells(prevRow, pasteColumnIdx), Cells(prevRow, pasteColumnIdx)).Select
            pasteColumnIdx = pasteColumnIdx + 4
            ActiveSheet.Paste
            strTemp = CStr(currentRow) + ":" + CStr(currentRow)
            Rows(strTemp).Select
            Selection.Delete Shift:=xlUp
            'currentRow = currentRow + 1
        Else
            prevRow = prevRow + 1
            currentRow = currentRow + 1
            pasteColumnIdx = 8
        End If
    Loop
    
End Sub

这篇关于将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！