Solved

VBA script to organize data into columns

Posted on 2015-02-11
5
106 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
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
Comment Utility
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:SimonAdept
Comment Utility
Hi Tom, was the above solution fit for purpose?
0
 

Author Comment

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

Author Comment

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

Author Closing Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now