tomfolinsbee
asked on
Excel script to organize similar data under one column
Hello Experts
I have one sheet with several thousand rows containing part measurement data. The parts are grouped by part type. The measurement units vary from group to group, so each column contains different types of measurements.
I would like some help with a script that will organize all similar data to a single column.
I've attached some sample data.
1. If cell in row H = "No.", then for each vertical array between column I:N and down to the next row where H=No., copy the vertical array of values over to the matching column 0:BE
2. If cells in column M or N are emtpy, then copy text over to columns BF and BG.
3. There should be a matching column in 0:BE for all other cases. If not, return error.
Thank you!
EE2015007.xlsx
I have one sheet with several thousand rows containing part measurement data. The parts are grouped by part type. The measurement units vary from group to group, so each column contains different types of measurements.
I would like some help with a script that will organize all similar data to a single column.
I've attached some sample data.
1. If cell in row H = "No.", then for each vertical array between column I:N and down to the next row where H=No., copy the vertical array of values over to the matching column 0:BE
2. If cells in column M or N are emtpy, then copy text over to columns BF and BG.
3. There should be a matching column in 0:BE for all other cases. If not, return error.
Thank you!
EE2015007.xlsx
ASKER
one time only. Thanks for your interest!
you say Copy block I:N till you hit the first No
to O:BE
I:N = 6 columns
O:BE = 31 columns
so if we copy will be
first block O:T
second block U:Z
etc...
last block finishes at column BD
and Be will be blank !!!
is this correct ?
gowflow
to O:BE
I:N = 6 columns
O:BE = 31 columns
so if we copy will be
first block O:T
second block U:Z
etc...
last block finishes at column BD
and Be will be blank !!!
is this correct ?
gowflow
ASKER
Idea is to copy each column in I:N separetely over to the column in O:BE with the matching header (measurement unit).
Thanks!
Thanks!
Matching header !!!! ???? You are kidding it is in chineese !
gowflow
gowflow
Hi, I gave you a solution to your last one, Tom, and am working on one for this. I expect to have something for you in the next few hours (after family time).
@gowflow: for the last one I did unicode character code matching, but this one is a little easier in some ways.
@gowflow: for the last one I did unicode character code matching, but this one is a little easier in some ways.
good luck then Simon I will leave you to handle translation as not into this.
gowlfow
gowlfow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simon
Thanks for this. Would you mind modifying so that there no labels in the top tow to start, and new labels are added whenever a new label is encountered will the script is looping through? I created the top row labels using excel remove duplicates, but could have missed some or perhaps ilchanged encoding???
Really appreciate your help with this!
Thanks for this. Would you mind modifying so that there no labels in the top tow to start, and new labels are added whenever a new label is encountered will the script is looping through? I created the top row labels using excel remove duplicates, but could have missed some or perhaps ilchanged encoding???
Really appreciate your help with this!
Hi Tom, with respect, that's a new request. This solution was based on matching to an existing range of headers and writing to fixed pair of column if M:N headers were blank. I'll happily help if you sign this one off and ask a new question, but this has taken enough time to solve, and what you're asking now seems like a significant change of scope.
Out of interest are you asking because you found lots of unmatched headers when you ran the code above on your live data?
How many rows of live data do you have to go through?
Out of interest are you asking because you found lots of unmatched headers when you ran the code above on your live data?
How many rows of live data do you have to go through?
ASKER
Thanks simon!
About 3000 rows.
I understand the error messages now. The data looks fine.
Thanks for your efforts on this!
About 3000 rows.
I understand the error messages now. The data looks fine.
Thanks for your efforts on this!
ASKER
Thanks your help with this!
ASKER
https://www.experts-exchange.com/questions/28614962/VBA-script-to-organize-data-into-columns.html
I've created a new question
1) error around record 600 in the full recordset
2) request to automatically generate new labels in top row
Thanks!
I've created a new question
1) error around record 600 in the full recordset
2) request to automatically generate new labels in top row
Thanks!
Is this a 1 time or repetitive ?
gowflow