Script to Transform Excel spreadsheet into tables

This is follow up question to one answered by Faustulus.

For the attached spreadsheet (Transform) which is generated by Faustulus' script, I would like to split it into these new tables. (total 3 output sheets)

New tables:

¿¿¿¿                                   FinishName    FinishCost
A1(¿¿)¿¿                                   NKL¿¿          2000
A1(¿)¿¿                                   NKL¿¿          2000
A1H53¿                               NKL¿¿          2000
A1HRB¿¿ - ¿A1H¿¿¿¿¿¿¿NKL¿¿          2000

¿¿¿¿                                   ¿¿¿¿¿          ¿¿¿¿¿G
A1(¿¿)¿¿                                   23.0                  14.00
A1(¿)¿¿                                   25.0                  16.00
A1H53¿¿                           27.0                  18.00
A1HRB¿¿ - ¿A1H¿¿¿¿¿¿¿29.0                  18.00
A1H¿                                   21.0                  12.00

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you need to split the shed after it was created? I.e. in a separate operation?
Or would it be acceptable to split the original table at the same time as transforming it's structure?
I haven't seen the attached workbook yet (using an iPad now) but if there are no samples of the two sheets I would appreciate your mentioning which columns (of the 'Transformed' sheet) go into which of the new sheets.
tomfolinsbeeAuthor Commented:
Hi Faustulus,

A single operation that generates three tables would be great.

I included samples for the new sheets.

Thanks for your help!

tomfolinsbeeAuthor Commented:
two new tables: tblPart and tblFinish
the first column, partname, goes into both tblPart and tblFinish.
the 2nd and 3rd column goes into tblPart
the rest of the columns go into tblFinish (column header goes into new field FinishName).

Hope this helps!

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

[ fanpages ]IT Services ConsultantCommented:

Is this question the same as another you currently have open?

"Excel VBA Script"
[ ]

This & the other question do appear to be the same set of requirements; although the other does not actually include details of what the three tables contain or how to determine this.

Thanks for your clarification.



[EDIT: 30/09/2013 11:35 (UK time) - The other question has now been marked for deletion ~ fp.]
tomfolinsbeeAuthor Commented:
Please find attached updated spreadsheet.

Please see attached spreadsheet "Current" that contains sample data.

Just to recap: I would like a script that will create 3 tables: tblPartFinish, tblPart and tblFinish.  

There is already a script there to create tblPartFinish (courtesy of Faustulus). THe script needs to be extended to create the other 2 tables.

The sheet is color-coded, so hopefully it is clear.

In the "real" data, there are some empty rows, so the script should keep checking for about 2,000 rows
I'm travelling today and tomorrow and intend to nurse my jet lag the day after. Meanwhile, I'm following the thread and will offer my tuppence' worth eventually if no other expert likes to help out earlier.
Thanks for waiting. The attached workbook is largely the same as before. Just run the procedure 'ReformatDatabase'. It is available from the Developer tab's 'Macros' button. In this procedure you can set the sheet names.
Const SheetNames As String = "Current,Transformed,Part,Finishes"
If you change the sequence of the sheets here you must also change the sequence of the related enumerations on top of the code sheet. Don't move the original (Current) sheet from first position, though!
The procedure first creates the sheets, except for the Current, and deletes any previously existing by the same names. Then it calls three procedures, each one populating one of the sheets. This is so that you can find your way about the code easily.
In the procedure 'CreateFinishSheet' you can change the column captions, and I have arranged the code so that you can probably adjust formatting without too much difficulty.
Let me know if you need any help with that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfolinsbeeAuthor Commented:
Wow! Worked perfectly the first time on the production data. Many groups of empty rows, so nice to see that the script was smart enough to skip over them... Thank you!

I was wondering if you could make one enhancement - could you add a PartID in the output tables = the row # in the source sheet?

Thanks again. I wish i could give you 5,000 points for this!
tomfolinsbeeAuthor Commented:
Thanks a lot Faustulus!

I'm going to create a new question, and post the link back here. Hope that is ok.


add a PartID in the output tables = the row # in the source sheet?
I don't understand. Could you show me somehow?
If it is an extra row you need in the original source sheet I wouldn't be surprised if you can just add it. The number of rows, most of the time, is determined by the first row containing data (under the caption and title rows) and the last row containing data. Add data and the last row shifts.
tomfolinsbeeAuthor Commented:

Sorry it wasn't clear... Basically, I just want to have a unique integer identifier to link the input and output tables (and preserve the original sort order of the source table).  I've refined the request a bit, please ignore what i wrote earlier about ROW#.

I was wondering if the the script could insert  a new first column "PartID" in the source sheet as well as the 3 output tables, and populate it with a series of integers from 1 to N, based on the original sort order of the source sheet.

Your questions were so well prepared it was a pleasure working with them. So, yes, I would indeed prefer this one to be a new question :-)
Basically, it is rather easy to insert a column with a serial number, but the method of numbering would be different for each of the sheets, or so it seems to me now. Therefore the program probably should create these numbers separately for each sheet rather than inheriting them from the Source. But this idea raises two questions of its own.
1. Do you need numbers in the Source sheet at all? Meaning, isn't this sheet superseded by the new derivatives?
2. By which system should the items in each sheet be numbered?
tomfolinsbeeAuthor Commented:
I will ask the team responsible for the Excel sheet to add a column called PartID in the first column of the source sheet. It will be a merged cell, just like the long part name (1:1 relationship between PartID and the PartName). It will be numbered sequentially from 1 to N, in the same sort order.

Then perhaps your script can add the new PartID in all three output sheet in the same way you add the PartName.  If there is no PartID, then just leave it blank in the output sheet.

In the test spreadsheet, perhaps you can just add the extra column in the source sheet and populate with dummy data?
tomfolinsbeeAuthor Commented:
will make the change to the spreadsheet, and post as a new question. Just a minute...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.