Select specific data from Excel spreadsheet and rearrange

Posted on 2013-10-08
Medium Priority
Last Modified: 2013-10-16
I have exported into MS Excel a list of detailed, inventory products. The words are in Spanish but the concept is the same. From the attachment, you can tell there are a lot of unnecessary and empty fields. Each product takes 14 rows and repeats the headers for each product. I need to collect just 6 of those fields with its data and rearrange in a vertical format. Attached is a sample of before and after. I need help with the formulas to make this happen. Thx.
Question by:xav1963
  • 2
LVL 25

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 39557155
You can use Index to find the cells you want in column A:D
See attached.
LVL 14

Expert Comment

ID: 39565460
Please try the code in the attached workbook.
All the code is in the module 'Converter' which you can drag to your own project in the VBE window. Run the procedure 'ConvertData' which you can access through the Developer->Macros list or from the VBE window.
At the top of this procedure you will find two worksheet names which you can change to match your actual workbook.
Set WsS = Worksheets("Sheet1")
Set WsT = Worksheets("Sheet2")
Note that Worksheet2 must be set up manually. The code doesn't do. it.
As for Sheet1 the original data must be in columns A:D and the first row of the first set of data must have the word "Código:" in column A of its first row. Datasets must be of 13 lines length with no intervening blanks.
The code is very unsophisticated, intended for one-time use only. I hope it can do the job for you.

Author Closing Comment

ID: 39575526
Sorry for late response.. was out-of-town ... I tried it and that worked simply and perfectly... thx.
LVL 14

Expert Comment

ID: 39576239
Did you try my solution as well?
I rather think it was too much work to go entirely without mention even if you didn't like it.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Virtualization software lets you run different versions of Windows, Ubuntu Linux and other versions of Linux all at the same time, rather than running each one directly from your computer's hard drive.
The Windows Firewall provides an important layer of protection and a rich interface to configure it. Unfortunately, it lacks item level filtering. This article details my process of implementing firewall-as-code to reduce GPO bloat.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question