CODEWITHSUNDEEP
excelvba
startfrom0
startfrom0

Reputation: 21

How to convert multiple rows into single row in excel?

I have data in table as below:

pre

I have data as above in table. This table has many MMID M000071,M000073,....etc. screeningterm might vary for assets. Every MMID may have several dates.

I want output as below:

after

There are 20 thousands in the table. So I want to find an easy way to do this job.

Upvotes: 1

Views: 4120

Answers (2)

startfrom0
startfrom0

Reputation: 21

@Jeeped give me a suggestion that use VBA.

So through several hours learning of Excel VBA, I have solved this problem and gain the answer that I want.

The code as below: 

Option Explicit

Public Sub sort()
    Dim j As Long
    Dim cursor As Long
    j = 2
    cursor = 2

    For j = 2 To 20150
X:      If Worksheets("Sheet2").Cells(cursor, "A").Value = "" Then
            Worksheets("Sheet2").Cells(cursor, "A").Value = Worksheets("Sheet1").Cells(j, "A").Value
            Worksheets("Sheet2").Cells(cursor, "H").Value = Worksheets("Sheet1").Cells(j, "D").Value
            Select Case Worksheets("Sheet1").Cells(j, "B")
                Case "ACR"
                    Worksheets("Sheet2").Cells(cursor, "B") = Worksheets("Sheet1").Cells(j, "C")
                Case "Microalbumin"
                    Worksheets("Sheet2").Cells(cursor, "C") = Worksheets("Sheet1").Cells(j, "C")
                Case "eGFR"
                    Worksheets("Sheet2").Cells(cursor, "D") = Worksheets("Sheet1").Cells(j, "C")
                Case "LDL_HDL_TotChol"
                    Worksheets("Sheet2").Cells(cursor, "E") = Worksheets("Sheet1").Cells(j, "C")
                Case "HbA1C"
                    Worksheets("Sheet2").Cells(cursor, "F") = Worksheets("Sheet1").Cells(j, "C")
                Case "UricAcid"
                    Worksheets("Sheet2").Cells(cursor, "G") = Worksheets("Sheet1").Cells(j, "C")
            End Select
        Else
            If Worksheets("Sheet1").Cells(j, "A").Value = Worksheets("Sheet2").Cells(cursor, "A").Value Then
                If Worksheets("Sheet1").Cells(j, "D").Value = Worksheets("Sheet2").Cells(cursor, "H").Value Then
                    Select Case Worksheets("Sheet1").Cells(j, "B")
                        Case "ACR"
                            Worksheets("Sheet2").Cells(cursor, "B") = Worksheets("Sheet1").Cells(j, "C")
                        Case "Microalbumin"
                            Worksheets("Sheet2").Cells(cursor, "C") = Worksheets("Sheet1").Cells(j, "C")
                        Case "eGFR"
                            Worksheets("Sheet2").Cells(cursor, "D") = Worksheets("Sheet1").Cells(j, "C")
                        Case "LDL_HDL_TotChol"
                            Worksheets("Sheet2").Cells(cursor, "E") = Worksheets("Sheet1").Cells(j, "C")
                        Case "HbA1C"
                            Worksheets("Sheet2").Cells(cursor, "F") = Worksheets("Sheet1").Cells(j, "C")
                        Case "UricAcid"
                            Worksheets("Sheet2").Cells(cursor, "G") = Worksheets("Sheet1").Cells(j, "C")
                    End Select
                Else
                    cursor = cursor + 1
                    GoTo X
                End If
            Else
                cursor = cursor + 1
                GoTo X
            End If
        End If
    Next

End Sub

I hope this can help someone else like me.

Note:You should set the sheet2's cell MESDATE as Text before run this program. Otherwise it will change the sheet1's cell MSEDATE to Date type.

Upvotes: 1

sandeep
sandeep

Reputation: 31

Use Text to column option in excel.

1) Copy Data to Excel

2) Select data

3) Under Data Ribbon -> Select Text to Column

4) Choose option Delimted and Click Next

5) Choose Delimter As Space (Check checkbox) and Click next

6) Choose destination and Press Finish

Step1

Step2

Step3

Step4

Upvotes: 0

Related Questions