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

[roseuz]

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.

[Olly]

Try this:

Please Login or Register  to view this content.

[roseuz]

Thanks, but can you please give me some guidance as to how to implement that into Excel. I have version 2007. Also, do you know if that would work with Excel alternative programs, like WPS (formerly Kingsoft) Spreadsheets.

[Olly]

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.

[roseuz]

Try this:

Please Login or Register  to view this content.

WOW. Beautiful. This works great. Thank you.

A few questions:

1) Is it possible for you to take the file path field and convert it onetime to a hyperlink using =hyperlink() so that I don't have to create an extra column in which I reference it.

2) If I add or delete fields, what do I have to change?

3) So, is using code via VBA window was the only way to accomplish this?


I was thinking that I don't really need Excel since there are so many free alternatives out there, but I forgot about things like this. It looks like Excel still has the upper hand.

[roseuz]

double post

[Olly]

1. Change the code to:

Please Login or Register  to view this content.

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.

[roseuz]

Thank you once again. I actually got the file portion link working before you submitted your answer by replacing the < > characters with the corresponding text. I however didn't think of the website address though and would have wanted that linked too. Plus, your way seems more efficient.

I was going to ask you what happens if I want to link a website without including the http://, but from my testing in Excel, it looks like it needs it, otherwise it thinks it's linking to a file.

A few more questions:

1) Why don't either of these codes work in other sheets for the same workbook? Sheet2, Sheet 3 -- it doesn't do anything.

2) I wasn't expecting a vba response, so I didn't mention that after each set of entries, which would be after the Notes field, I have one line underneath it that's something like this:
---------------------------

So, to be clear, starting with the field before notes, it looks like this:

Website=

Notes=
--------------------------
Name (it starts over here)

Portable

However, I see it doesn't make a difference with your code. It just gets deleted (which is good) and everything is where it belongs. Why is that? Is it because the code is looking at every other line?

3) Not related, but wouldn't mind your thoughts. I took Visual Basic in college years ago. It was the first program I was introduced to and I loved it. Unfortunately I never learned VB.net as it wasn't taught at my college. I have also done very little programming since then, though I did learn other programs during my college years. With more and more programs being created for phones and as apps for Windows 8, rather than traditional desktop programs, do you think it's worth it--career wise and money wise--to learn VBA and/or VB.net? Is VBA and/or VB.net in much demand and if it is will it be around for at least a decade or two?

[Olly]

1. The code specifies which worksheets to look at:

Please Login or Register  to view this content.

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

[roseuz]

Thank you!

I have added to your reputation with a response of approved.

[Olly]

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.

[roseuz]

O.k. Well, I will wait for a few days before marking it as solved (though yours is a solution and a great one) because I am curious about:

1) If there is a way to get the code to work with any or multiple sheets without me having to change the name.

2) Would like to see what other non code solutions exist, if any, and how difficult they are.

P.S. Can people still comment after a thread is marked as solved?

[Olly]

1. Of course. What's your requirement?

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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.

[roseuz]

YEAH!!! Now I am very happy. Activesheet is exactly what I wanted--no need now at all for me to change a thing in the code, I can name the sheet to whatever I want without worry, and if I ever want the code for another workbook, I only have to copy and paste.