Is it Possible to easily convert rows to columns and also extract the headers?

Hello. I have data that is written in the manner later demonstrated below, with blank lines after each entry and with some fields being blank, such as the Notes field in the 2nd set. When I paste it into Excel, it all gets pasted in Column A across multiple rows:

Name= the name

Portable= Y

File / Folder Path= <file:C:\Programs\Utilities\littleprogram.exe>

Website= http://somewebsite.com

Notes= relocate later

Name= Photo Editor

Portable= N

File / Folder Path= <file:C:\Downloads\>

Website= http://adomain.com

Notes=

And so on. I really wanted a colon after the fields, like Name: Portable: and so on, but put an equal sign because in location I am specifying file paths which have colons and make things complicated when / if I need to do a find and replace.

So, I think you can figure out by now, what I want is for the above data to look like this:

A1:Name B1:Portable C1:File / Folder Path D1:Website E1:Notes
A2:The Name B2: Y C2:<file:C:\Programs\Utilities\littleprogram.exe> D2:http://somewebsite.com E2: great program
A3: Photo Editor B3:N C3:<file:C:\Downloads\> D3: http://adomain.com E3:

As you can see, some fields, such as Notes, will sometimes be blank.

I am able to do what I want to do, but with a lots of steps using MS Word and/or Excel to do find and replace, separating the fields via the equal sign, and so on. I came up with two approaches, but they are very tedious.

This question over here: http://www.excelforum.com/excel-form...n-headers.html, while different than what I am asking for, gave me hope that maybe there is a solution for what I am asking for.

Extra Info: For the file path, I plan on removing the < > symbols via the find and replace function, and then using the hyperlink function in another column that references the column where the file path is at so that it can be linkable. If anyone has any ideas on this, that would also be appreciated.

O.k. Thanks.

Try this:

Sub foo()
Dim lRowSrc, lRowTgt As Long, lCol As Long
Dim i As Integer
Dim s As String, stHeader As String, stValue As String
Dim c As Range

With Sheet1 'change if necessary
    lRowTgt = 1
    For lRowSrc = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
        s = .Cells(lRowSrc, 1).Value
        i = InStr(s, "=")
        If i > 0 Then
            stHeader = Left(s, i - 1)
            stValue = Right(s, Len(s) - i)
            Set c = .Range("1:1").Find(what:=stHeader, lookat:=xlWhole)
            If Not c Is Nothing Then
                lCol = c.Column
            Else
                With .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
                    .Value = stHeader
                    lCol = .Column
                End With
            End If
            If stHeader = .Cells(1, 2).Value Then lRowTgt = lRowTgt + 1
            .Cells(lRowTgt, lCol).Value = Trim(Replace(Replace(stValue, "<", ""), ">", ""))
        End If
    Next lRowSrc
    .UsedRange.Columns.AutoFit
    .Columns(1).EntireColumn.Delete
End With
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

This will NOT work in alternative spreadsheet programs, only in Excel.

1. Change the code to:

Sub foo()
Dim lRowSrc, lRowTgt As Long, lCol As Long
Dim i As Integer
Dim s As String, stHeader As String, stValue As String
Dim c As Range

Application.ScreenUpdating = False
With Sheet1 'change if necessary
    lRowTgt = 1
    For lRowSrc = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
        s = .Cells(lRowSrc, 1).Value
        i = InStr(s, "=")
        If i > 0 Then
            stHeader = Left(s, i - 1)
            stValue = Trim(Replace(Replace(Right(s, Len(s) - i), "<", ""), ">", ""))
            Set c = .Range("1:1").Find(what:=stHeader, lookat:=xlWhole)
            If Not c Is Nothing Then
                lCol = c.Column
            Else
                With .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
                    .Value = stHeader
                    lCol = .Column
                End With
            End If
            If stHeader = .Cells(1, 2).Value Then lRowTgt = lRowTgt + 1
            With .Cells(lRowTgt, lCol)
                .Value = stValue
                If Left(stValue, 5) = "file:" Or Left(stValue, 5) = "http:" Then
                    .Hyperlinks.Add anchor:=Range(.Address), Address:=stValue
                End If
            End With
        End If
    Next lRowSrc
    .UsedRange.Columns.AutoFit
    .Columns(1).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub

This will add hyperlink to all cell values which start FILE: or HTTP:


2. You don't need to change anything - the code is written to cater for varying field headers. It will loop through all the data in column A, and search for a matching header in Row 1. If the matching header is in Column B, it starts a new row for the data output. If the filed header doesn't already exist in Row 1, it will be added in a new column.

3. It's probably not the ONLY way, but it's almost certainly the fastest and simplest way.

1. The code specifies which worksheets to look at:

With Sheet1 'change if necessary

2. The code looks at every row of data in Column A, but only transfers values for cells containing the '=' sign. text to the left of the = sign defines the field header, to the right of the = sign defines the value.

3. I can't really comment. I'm not a developer, I just find it useful to know a bit of VBA. Do what you enjoy, would be my advice

Thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

1. Of course. What's your requirement?

If you want it to run on the active (currently selected) sheet, then change this line

With Sheet1 'change if necessary

to

With Activesheet

Or it's easy to loop through multiple worksheets.... Give us a specific requirement.

2. Yes, people CAN still comment after a thread has been marked solved; but tbh people are less likely to open a solved thread, with a view to giving advice.