tomfolinsbee
asked on
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:
tblFinish
¿¿¿¿ FinishName FinishCost
A1(¿¿)¿¿ NKL¿¿ 2000
A1(¿)¿¿ NKL¿¿ 2000
A1H53¿ NKL¿¿ 2000
A1HRB¿¿ - ¿A1H¿¿¿¿¿¿¿NKL¿¿ 2000
tblPart
¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿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
Thanks!
EXX-130926-Re-format-DatabaseV2.xlsm
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:
tblFinish
¿¿¿¿ FinishName FinishCost
A1(¿¿)¿¿ NKL¿¿ 2000
A1(¿)¿¿ NKL¿¿ 2000
A1H53¿ NKL¿¿ 2000
A1HRB¿¿ - ¿A1H¿¿¿¿¿¿¿NKL¿¿ 2000
tblPart
¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿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
Thanks!
EXX-130926-Re-format-DatabaseV2.xlsm
ASKER
Hi Faustulus,
A single operation that generates three tables would be great.
I included samples for the new sheets.
Thanks for your help!
-Tom
A single operation that generates three tables would be great.
I included samples for the new sheets.
Thanks for your help!
-Tom
ASKER
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!
-Tom
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!
-Tom
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
Is this question the same as another you currently have open?
"Excel VBA Script"
[ https://www.experts-exchange.com/questions/28253204/Excel-VBA-Script.html ]
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.
BFN,
fp.
[EDIT: 30/09/2013 11:35 (UK time) - The other question has now been marked for deletion ~ fp.]
Is this question the same as another you currently have open?
"Excel VBA Script"
[ https://www.experts-exchange.com/questions/28253204/Excel-VBA-Script.html ]
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.
BFN,
fp.
[EDIT: 30/09/2013 11:35 (UK time) - The other question has now been marked for deletion ~ fp.]
ASKER
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
EL-20130930.xlsm
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
EL-20130930.xlsm
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
Thanks a lot Faustulus!
I'm going to create a new question, and post the link back here. Hope that is ok.
Cheers,
Tom
I'm going to create a new question, and post the link back here. Hope that is ok.
Cheers,
Tom
Hi,
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.
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.
ASKER
Hi
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.
Thanks!
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.
Thanks!
Tom,
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?
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?
ASKER
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?
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?
ASKER
will make the change to the spreadsheet, and post as a new question. Just a minute...
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.