Thread: Extracting Complex Raw Data/Macros/Text-to-Columns

I need to extract a large amount of raw data from a certain program and plug it into a spreadsheet in a particular format. Doing the process manually is laborious in the extreme and I want to find some way to automate the process; or at the very least, eliminate as much of the manual work as possible.


A sample of the raw data is below in the form of a screenshot. Each entry is normally 14 lines --- there are some exceptions, but if I could even automate it based on the "14-line standard" and then manually enter the exceptions, that wouldn't be so terrible.

The second screenshot shows you my attempt to take the data and use Excel's "Text to Columns" feature using tab and space delimiters to make the data simpler to grab. Unfortunately, there is a problem with this method, as I need to grab the "Organization Name" and as you can see in the three examples, there are often spaces (e.g. "University of North Carolina at Chapel Hill"), so Excel will put each word into its own cell. Also note that in my second screenshot, I have highlighted all the data I need to grab in yellow.

In order to try to fix the issue with the space delimiters, I found that I could use the Text-to-Columns feature with fixed-width on the first two rows and then use a tab/space delimiter for the other 12 rows of data. You can see an example of this in the third screen capture below. Unfortunately, I don't know if there's a way to automate this process or not.


So basically, I need six items out of this raw data and I'm trying to find the shortest route to getting there.


Here are a few questions I have:

(1) What's the simplest solution to my problem?
(2) Should I use a macro?
(3) Can I create a macro that will automatically use the fixed-width Text-to-Columns for the first two rows and then tab/space delimited for the next 12 rows?
(4) Is there a way to do a repeating loop with Macros?
(5) Are there any formulas I am missing that would make this process simpler?
(6) Instead of trying to format the data, is there a way I could simply paste it into Excel "raw" and use some sort of function that would automatically find the data I need?

Any suggestions? Ideas?

Or perhaps another way to ask this would be:

How do I use a Macro to complete one action on the first two rows of data and another action on the next 12 --- and then make it repeat over and over again?

Here is my output after recording this action twice:

' Macro3 Macro
' Macro recorded 10/2/2009 by DHHS
'

'
Range("A1:A2").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(26, 1), Array(42, 1)), _
TrailingMinusNumbers:=True
Range("A3:A14").Select
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:= _
True
Range("A15:A16").Select
Selection.TextToColumns Destination:=Range("A15"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(26, 1), Array(42, 1)), _
TrailingMinusNumbers:=True
Range("A17:A28").Select
Selection.TextToColumns Destination:=Range("A17"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:= _
True
Range("I29").Select
End Sub

How would I alter this code to always do 2 rows one way, then the next 12 another way, and repeat over and over?

Please change the QUOTE tags to CODE tags.

I doubt you'll get any help posting pictures; nobody is going too type in data from them, and no one could test anything they did without data.

If the data is received as a text file, post that, along with a workbook showing an example of desired (and corresponding) output.