asked on

Select specific data from Excel spreadsheet and rearrange

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.
Products.xls

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Faustulus

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.
EXX-131011-Convert-Data-To-Singl.xls

xav1963

ASKER

Sorry for late response.. was out-of-town ... I tried it and that worked simply and perfectly... thx.

Faustulus

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.