Solved

VBA script to organize data into columns

Posted on 2015-02-11
5
111 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:
Simon earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

862 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

21 Experts available now in Live!

Get 1:1 Help Now