user6622113 user6622113 - 4 months ago 12
MySQL Question

Create new Excel rows based on column Heading and populate the data

I have to create output XLS file based on input xls file header. I am giving below exact requirement. hope its clear. if not then please let me know.

Iput XLS -->
ID Version NameLegacy ProjectNumber OwnerName Language Keywords OwnerSite External Content Relevance Periodic Coremap ValidTo
1 1 Mohan 1000 x ENG ABCD AB No ok no 5 2017-10-14 2018-10-14
2 2 Shayam 1001 y ENG EFGH BC No ok yes 2 2017-10-14 2018-10-14
3 1 Sourabh 1002 z ENG IJKL CD Yes no no 4 2017-10-14 2018-10-14


Based on this Input XLS File, i need Output XLS File as below. Each row of input file will have 12 row of output file and respective value will be filled in Output file from input file.

ID Version IBANAME STRINGVALUE INTEGERVALUE FLOATVALUE FLOATVALUEWITHUNITS BOOLVALUE TIMEVALUE URLVALUE REFERENCEVALUE
1 1 NameLegacy Mohan
1 1 ProjectNumber 1000
1 1 OwnerName x
1 1 Language ENG
1 1 Keywords ABCD
1 1 OwnerSite AB
1 1 External No
1 1 Content ok
1 1 Relevance no
1 1 Periodic 5
1 1 Coremap 2017-10-14
1 1 ValidTo 2018-10-14
2 2 NameLegacy Shayam
2 2 ProjectNumber 1001
2 2 OwnerName y
2 2 Language ENG
2 2 Keywords EFGH
2 2 OwnerSite BC
2 2 External No
2 2 Content ok
2 2 Relevance yes
2 2 Periodic 2
2 2 Coremap 2017-10-14
2 2 ValidTo 2018-10-14
3 1 NameLegacy Sourabh
3 1 ProjectNumber 1002
3 1 OwnerName z
3 1 Language ENG
3 1 Keywords IJKL
3 1 OwnerSite CD
3 1 External Yes
3 1 Content no
3 1 Relevance no
3 1 Periodic 4
3 1 Coremap 2017-10-14
3 1 ValidTo 2018-10-14


Thanks in advance.

Answer

There are a few steps to do this using VBA. The main purpose is to identify what you are actually trying to do.

It seems like you are attempting to sort through records to identify what the format of the information is and place it in a column designated for only that format. To do this, I have created a very simple custom Function..

This function takes in the name of the header as a string, and uses Select/Case to find appropriate matches for which column the target sheet needs. This is custom to your specific layout. Feel free to adapt or change it.

FUNCTION TO FIND CORRECT COLUMN ON TARGET

Function ColFinder(header As String) As Long


'Target Column Format = Column #

'"STRINGVALUE"        = 4
'"INTEGERVALUE"       = 5
'"FLOATVALUE"         = 6
'"FLOATVALUEWITHUNITS"= 7
'"BOOLVALUE"          = 8
'"TIMEVALUE"          = 9
'"URLVALUE"           = 10
'"REFERENCEVALUE"     = 11


' **** SET VALUES FOR CASES TO MATCH DESIRED FORMAT ABOVE  ****

'Default to Column 4 StringValue if unknown
    Select Case header
        Case "NameLegacy"
            ColFinder = 4
        Case "ProjectNumber"
            ColFinder = 5
        Case "OwnerName"
            ColFinder = 4
        Case "Language"
            ColFinder = 4
        Case "Keywords"
            ColFinder = 4
        Case "OwnerSite"
            ColFinder = 10
        Case "External"
            ColFinder = 8
        Case "Content"
            ColFinder = 11
        Case "Relevance"
            ColFinder = 8
        Case "Periodic"
            ColFinder = 5
        Case "Coremap"
            ColFinder = 9
        Case "ValidTo"
            ColFinder = 9
    End Select

End Function

Using a few other Functions for finding Last Row & Last Column

Last Row Function

Function lastRow(sheet As String) As Long

    lastRow = Sheets(sheet).Cells(Rows.Count, "A").End(xlUp).Row    'Using Cells()

End Function

Last Column Function

Function lastCol(sheet As String) As Long

    lastCol = Sheets(sheet).Cells(1, Columns.Count).End(xlToLeft).Column

End Function

With these three functions, you can run a simple loop through the source sheet, one row at a time, then look to see which column the data is coming from, and place it on the appropriate target.

This example is using two worksheets in the same workbook. To change, just extend the address to include other workbooks or sheets. This is just the simplest way to show the concept.

CODE TO LOOP THROUGH SHEETS

Sub ColsToRows()

Dim tRow As Long, sCol As Long, sRow As Long
Dim source As String, target As String
Dim tString As String

source = "Sheet1"
target = "Sheet2"

'Set target Row starting point
tRow = 2

    'Loop through Source Rows with Data, starting after Header Row
    For sRow = 2 To lastRow(source)

        'Loop through each header in Source Row
        For sCol = 3 To lastCol(source)

            'Set Basic Columns on Target
            Sheets(target).Cells(tRow, 1) = Sheets(source).Cells(sRow, 1)
            Sheets(target).Cells(tRow, 2) = Sheets(source).Cells(sRow, 2)

            'Get Header Name as Temp String to find which format it requires
            tString = Sheets(source).Cells(1, sCol)

            'Label Column 3 of Target
            Sheets(target).Cells(tRow, 3) = Sheets(source).Cells(1, sCol)

            'Find Appropriate Column using custom Formula [ColFinder(header As String) As Long]
            Sheets(target).Cells(tRow, ColFinder(tString)) = Sheets(source).Cells(sRow, sCol)

            'Advance to next Target Row
            tRow = tRow + 1

        Next sCol

    Next sRow

End Sub

Source Sheet Example Source Sheet

Target Sheet Example Target Sheet