What I want Vs What I need?

[Que]

I have a text file that I cleaned up to a point. The cleaned version o
this text file exceeds Excel’s capacity in rows so Access is being use
to create my rows. My problem is that the data is not organized like
need it. (Columns) I have attached a spreadsheet showing what I hav
Vs What I need. Any help would be much appreciated

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48008

 

[Frank Kabel]

Hi
not much error checking included but try the following macro (watch for
linebreaks). Adapt this to your needs. Mae assumptions:
1. New item is identified by 'A' as first character in a line
2. Source Data is in column B starting in row 3
3. Details are identified by a preceding line with the content
'Line/Rel Item'


-------------------

Option Explicit
Sub extract_data()
Dim source_wks As Worksheet
Dim target_wks As Worksheet
Dim source_range As Range
Dim lastrow As Long
Dim src_rowindex As Long
Dim trg_rowindex As Long
Dim str_identifier As String
Dim lineitems As Variant
Dim item_index As Integer
Dim trg_colindex As Integer
Dim neg_flag As Boolean


Set source_wks = ActiveSheet
Set target_wks = Worksheets("Sheet2")
lastrow = source_wks.Cells(Rows.Count, "B").End(xlUp).Row
trg_rowindex = 1
src_rowindex = 3

While src_rowindex <= lastrow
If Left(Cells(src_rowindex, 2).Value, 1) = "A" Then
str_identifier = Left(Cells(src_rowindex, 2).Value, 9)
src_rowindex = src_rowindex + 1
While InStr(Cells(src_rowindex, 2).Value, "Line/Rel Item") = 0
_
And src_rowindex <= lastrow
src_rowindex = src_rowindex + 1
Wend
src_rowindex = src_rowindex + 1
While Left(Cells(src_rowindex, 2).Value, 1) <> "A" _
And src_rowindex <= lastrow
target_wks.Cells(trg_rowindex, 1).Value = str_identifier
lineitems = Split(Cells(src_rowindex, 2).Value, " ")
trg_colindex = 2
For item_index = 0 To UBound(lineitems)
If lineitems(item_index) <> "" Then
If lineitems(item_index) = "-" Then
neg_flag = True
Else
If neg_flag Then
target_wks.Cells(trg_rowindex,
trg_colindex).Value = _
lineitems(item_index) * (-1)
neg_flag = False
Else
target_wks.Cells(trg_rowindex,
trg_colindex).Value = _
lineitems(item_index)
End If
End If
If Not neg_flag Then trg_colindex = trg_colindex +
1
End If
Next
src_rowindex = src_rowindex + 1
trg_rowindex = trg_rowindex + 1
Wend

End If
Wend
End Sub