Solved

VBA script to organize data into columns

Posted on 2015-02-11
5
123 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

730 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