Help to improve a report imported as text

[Clarkey]

Hi,

I hope I can explain this one. I have a report from an external sourc
which is imported as text into excel. I have used the text impor
wizard to organise into columns as best as possible. However, in orde
that I can use the data to create pivot tables etc. there needs to b
some further re-organisation of the data. For example, assume that
simplified version of the report may look like:

Location 1
-------------
Asset Value
001 100.00
002 150.00
003 125.00
[Row of text]
[Row of text]
Location 2
-------------
Asset Value
010 200.00
050 175.00
025 180.00
005 500.00

Imagine this for thousands of rows with hundreds of locations. Th
number of rows of text between each location can vary (these represen
report headers/footers from the external source). I want to reorganis
the data into the following database-style format:

Location Asset Value
1 001 100.00
1 002 150.00
1 003 125.00
2 010 200.00
2 050 175.00
2 025 180.00
2 005 500.00

Any ideas on how this can be done automatically?

Many thanks in advanc

 

[Bernie Deitrick]

Clarkey,

Assuming your data is in two columns (A and B), insert two new columns A&B,
and a new row 1, so that your data is in C&D shifted down one row.

In cell A1, enter the word "Keep". In cell B1, enter the word "Location",
in C1 "Asset", in D1 "Value"

In Cell A2, enter the formula
=NOT(ISERROR(VALUE(C2)))
In Cell B2, enter the formula
=IF(LEFT(C2,8)="Location",MID(C2,FIND(" ",C2)+1,10),B1)
Copy A2:B2 down to match all your data, then copy and pastespecial values to
convert the formulas to values.

Sort based on column A and B, delete all rows with FALSE in column A, then
delete column A and you're done.

If you are going to do this a lot, record a macro and modify to work with
any size data set.

HTH,
Bernie
MS Excel MVP

 

[Otto Moehrbach]

Hi Clarkey
What you want can be done with VBA. It's just a matter of writing code
to suit your particular data layout.
If you wish, send me, via email, a sample file with a few hundred rows
of representative data. The key word here is "representative". Include as
many variations of the data as you can find. Looking at the data layout of
what you posted, the code may need access to a list of all the possible
locations. Include such a list with the locations written exactly as they
are written in your data..
My email address is (e-mail address removed).
Remove "cobia97" from this address. HTH Otto

 

[Clarkey]

Thanks for that suggestion Bernie, it seems like a practical solutio
and will give it a try.

Otto - I would also like to explore your proposed method using VBA
Unfortunately, I would prefer not to send the file since it contain
sensitive company information. However, would it be possible for you t
give some sample code just based around the trivial example I gave?
might then be able to adapt something suitable.

Many thanks again,
Clarke

 

[Otto Moehrbach]

Clarkey

Obviously, your suggestion has a boatload of pitfalls but I will
do as you say. Perhaps you can clarify some points about your example so
that what I give you will have some value to you.

1. Your example shows a dashed line in the cell immediately
below each Location cell. Is this the case for any of the Location cells in
your actual data? For all Location cells? For some?

2. Are there any blank cells within your data?

3. Your example shows numbers and "[Row of text]". Do any
of the "[Row of text]" look like a number (whole entry). I'm not asking if
they are numbers, just if they look like numbers.

4. Regarding the entries you have as "Location x". Do any
of those look like a number (whole entry)? Do they all start with the word
"Location"?

Otto

 

[Otto Moehrbach]

Clarkey
Here is some code that will do what you want with the data layout you
furnished in your OP. Note that there are no error traps written in the
code.
Your original data is assumed to be in Columns A & B. The result is
placed in Columns D:F. HTH Otto
Option Explicit
Dim ColARng As Range 'Rng of Column A
Dim FoundLoc As Range 'Found cell with "Location*"
Dim FoundNextLoc As Range 'Next cell with "Location*"
Dim Loc As String 'The location # in the "Location*" cell
Dim CancelA As Boolean
Sub ReArrangeData()
Set ColARng = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set FoundLoc = ColARng.Find(What:="Location*",
After:=ColARng(ColARng.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
CancelA = False
Do Until CancelA = True
Set FoundNextLoc = ColARng.Find(What:="Location*", After:=FoundLoc,
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
'If FoundLoc is the last "Location*" cell
If FoundNextLoc.Row <= FoundLoc.Row Then
Set FoundNextLoc = ColARng(ColARng.Count)(2)
CancelA = True
End If
Loc = Right(FoundLoc, Len(FoundLoc) - 9)
Call CutData
Set FoundLoc = FoundNextLoc
Loop
End Sub
Sub CutData()
Dim c As Long 'Counter for cells with numbers
c = 0
For c = FoundLoc.Row + 3 To FoundNextLoc.Row - 1
If IsNumeric(Cells(c, 1)) Then
Range("D" & Rows.Count).End(xlUp)(2) = Loc
Cells(c, 1).Resize(, 2).Copy Range("D" & Rows.Count).End(xlUp)(,
2)
End If
Next
End Sub

 

[Clarkey]

Otto,

Hopefully with that sample code I get can exactly what I need. You
time has been greatly appreciated on my part.

Best regards
Clarke