?
Solved

VBA script to organize data into columns

Posted on 2015-02-11
5
Medium Priority
?
137 Views
Last Modified: 2015-02-14
This is an extension to this question..
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28612259.html

I've attached the full record set. There is an error around row 600.

Also, would it be possible to modify the script so that it it automatically generates new labels in top row, rather than defining them in advance?

Thanks!
EE20150211.xlsm
0
Comment
Question by:tomfolinsbee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40604449
Hi Tom,

The error in that row appears to be because the first character in the script in that cell is somehow equivalent to an equals sign. I got round that by prefixing the value with a single quote when writing it.

The other issue, the ones it is flagging as you run through the code are all cells with data in but a blank header (so nothing to match against). There is no value in adding extra columns as we go because the extra column headers would be blank and never matched again. It only occurs for the cells listed below...
No match for header of cell $L$118 with value 01_(14_)______02_(B572)
No match for header of cell $L$119 with value 03____(B605)______04_(B701)
No match for header of cell $L$120 with value 05____(C629)______06_(C716)
No match for header of cell $L$121 with value 07__(G708)______08_____(G719)
No match for header of cell $L$122 with value 09____(K714)______0A_(K906)
No match for header of cell $L$123 with value 0B____(R507)______0C_(R662)
No match for header of cell $L$124 with value 0D_______(S156)
No match for header of cell $L$125 with value 0E_(S424)_____0F_(Y601)
No match for header of cell $L$784 with value 2875____4___H3.2
No match for header of cell $L$790 with value 0843_________H3.25
No match for header of cell $L$827 with value GHQ_______H_4.3
No match for header of cell $L$828 with value ____No.2_____H_3.6
No match for header of cell $L$1412 with value ____
No match for header of cell $L$1413 with value ____
No match for header of cell $K$1665 with value _SUS304CSP-H)
No match for header of cell $L$1694 with value ____W_5.7
No match for header of cell $K$1877 with value __18mm_________
No match for header of cell $K$1878 with value __20mm_________
No match for header of cell $L$2391 with value 7___
No match for header of cell $L$2410 with value _____________
No match for header of cell $L$2411 with value ____4.6
No match for header of cell $L$2416 with value ____4.6
No match for header of cell $L$2417 with value _3.0_1.5

Open in new window

... so I decided to give columns 3 & 4 (K&L) the same special handling as you had already specified for columns 5 & 6 (M&N) - i.e. all values in those columns where the header row was blank get written to a set column, so you now have "BLANK K" and "BLANK L" columns as well as "BLANK M" and "BLANK N" columns at the right hand side of the spreadsheet.

Here's your full set of data returned with the data correctly grouped into columns.
EE20150211-v1.xlsm

In summary, I haven't done exactly as you asked, partly because it wouldn't have solved the current problems. I have checked that all the columns headers you previously are populated in at least one case.

I can revise it further to add column headers dynamically, but for a one-shot deal I thought it more important to get through the full dataset without error.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40609496
Hi Tom, was the above solution fit for purpose?
0
 

Author Comment

by:tomfolinsbee
ID: 40609639
Yes very similar, except rows/columns are different too.
0
 

Author Comment

by:tomfolinsbee
ID: 40609641
Sorry, i thought i already replied to this question. Yes, it worked fine!
0
 

Author Closing Comment

by:tomfolinsbee
ID: 40609643
Thanks again for a quick and accurate reply to my question!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

800 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