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
Solved

VBA script to organize data into columns

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

808 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