Code to Sort Randum Data to new Worksheet

[Randy11]

Have some random data that needs to be screened and sorted into a specific format. Sample Data & Current Code below.
All Data is on Sheet2 in Range A1:A10000.
Want data to be screened line by line & copied into Sheet1 in the following format:
ID with Name Following in Same Cell i.e. 0E:57301 Snarley (Note: 0E is consistenty on every data line with a name)
Then next line to have Address with floor folowing i.e.
123 Yarboro Blvd Home Front + Basement + Sub-Basement + Yogic_Den
then onto next ID & repeat for entire range. (Data is random so sometimes Address & floor are already on same line, sometimes not.) Spacing also varies.... might be one blank line between data might be 2 or 3. Sample code will bring data into another sheet but will not be sorted in order needed. Any assistance appreciated. Believe what I need to to loop through the keywords for every line in the data to get into correct order. Also would need to Join some data together i.e. Address & floor when not already on the same line.... Ideas Samples Appreciated


Data Sample:
Turbu-Lockit - Systems
19/04/2012 8:52:01 AM
Slip
Page 1 / 41
Slip
0E:57301 Snarley
Site Level
Location / Location Entrance level

123 Yarboro Blvd

Home Front + Basement + Sub-Basement + Yogic_Den

0E:57308 Bogo
Entrance level

Location / Location

Entrance level

123 Yarboro Blvd

Home Front + Basement + Sub-Basement + Yogic_Den

0E:57309 Aardvark
Entrance level

Location / Location

Entrance level

123 Yarboro Blvd

Home Front

0E:57314 Canary

Entrance level
Location / Location

Entrance level

123 Yarboro Blvd

Home Front + Basement + Sub-Basement + Yogic_Den

0E:57328 Kithchen

Entrance level
Gateway / Site Entrance level
123 Yarboro Blvd Home Front + Basement
0E:57330 Bo Didily
Entrance level
Location / Location Entrance level

123 Yarboro Blvd

Home Front + Basement + Sub-Basement + Yogic_Den

0E:57332 Snapper

Entrance level
Location / Location

Entrance level

123 Yarboro Blvd

Home Front


Sample Code
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long
Dim NewSh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Fill in the search Value
MyArr = Array("0E", "123 Yarboro", "Home Front")

'You can also use more values in the Array
'myArr = Array("@", "

http://www")

'Add new worksheet to your workbook to copy to
'You can also use a existing sheet like this
'Set NewSh = Sheets("Sheet2")
Set NewSh = Sheets("Sheet1")

With Sheets("Sheet2").Range("A1:C10000")

Rcount = 0

For I = LBound(MyArr) To UBound(MyArr)

'If you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "@"
'Note : I use xlPart in this example and not xlWhole
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rcount = Rcount + 1

Rng.Copy NewSh.Range("A" & Rcount)

' Use this if you only want to copy the value
' NewSh.Range("A" & Rcount).Value = Rng.Value

Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[UnsolvedCoding]

I'm a bit confused, could you explain it a different way?

 

[Randy11]

No problem to explain, so far problem to fix.

Worksheet I am working on has the random data noted above.
Want to sort this data to another worksheet in the following order.
0E:57301 Snarley
Then next line to have Address with floor following in same cell i.e.
123 Yarboro Blvd Home Front + Basement + Sub-Basement + Yogic_Den

So would sort to look like this...
0E:57301 Snarley
123 Yarboro Blvd Home Front + Basement + Sub-Basement + Yogic_Den

Any assistance appreciated....

 

[SkipVought]

SORT works only in a table on one sheet, not from one sheet to another.

Your examples are unintelligible.

Please be CLEAR, CONCISE, COMPLETE and COGENT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

It's always going to be tedious to parse a report (which seems to be what you have). If you could work on the source of that report you'd be better off.

If you have to work with the report consider telling us:
as you move through the data how do you recognize where a new dataset starts?
what features are reliably consistent across datasets? That is, does address always come first (or forth)?
what features, if any, are not needed?

_________________
Bob Rashkin

 

[Randy11]

Thanks for the kind words Skip, you have a way to cut to the chase.... Perhaps the word Sort does not fit....
Would like to look at data in source worksheet & pull from it only specific information and place it in another worksheet in a specific order.
Info On Source Data, Critcal Data lines are:
1) 0E:xxxxxx Number followed by name which is always on the same line in the source worksheet.
2) Next line following that with data of interest is the address line in the sample 123 Yarboro Blvd
3) Line with Floor information sample; Home Front + Basement + Sub-Basement + Yogic_Den
4) Source Data is in order as it relates to each account. So if picked up line by line in order & placed in the 2nd worksheet it would maintain correct order. i.e. Look at source data pick up 0E:######, then address, then floor & place these in the new worksheet, move on to next 0E:######, address, floor etc etc
5) Only problems with source data is sometimes address info & floor info can be on same line in data & the number of blank rows in data can vary from 0 to 5 in between lines of data.
6) Unable to alter source of data as a better option.

Info on desired outcome in other worksheet:

Create two lines of data in other worksheet for each 0E:#######
Line 1) 0E:########
Line 2) Address with floor information following in same cell

Key words to locate needed Data:
("0E", "123 Yarboro", "Home Front")

Specifics needed for code:
Current code will pull all the needed data into a separate worksheet but does this by searching all date fist for 0E:, this is then placed in a list, followed by the Addresses, followed by the Floor info. So the correct order is lost.
Current code produces:
0E: 1233445
0E: 1256235
address
address
floor info
floor info
Want to Produce:
0E: 1233445
address floor info
0E: 1256235
address floor info

 

[Andrzejek]

Is it correct to assume your data (skipping empty lines) should be divided like this (by color):

Data Sample:
Turbu-Lockit - Systems
19/04/2012 8:52:01 AM
Slip
Page 1 / 41
Slip[blue]
0E:57301 Snarley
Site Level
Location / Location Entrance level
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den [/blue][red]
0E:57308 Bogo
Entrance level
Location / Location
Entrance level
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den [/red][green]
0E:57309 Aardvark
Entrance level
...[/green]

If so, what info do you want to keep/allign/etc and what info do you want to discard? Do you want to keep: "Site Level
Location / Location Entrance level" parts?

Have fun.

---- Andy

 

[SkipVought]

Key words to locate needed Data:
("0E", "123 Yarboro", "Home Front")

Or are you saying that EVERY address wiil start with "123 Yarboro" and nothing else and that your next row of interest will ALWAYS begin with "Home Front"? If so, you could use a simple IF() formula on the sheet to identify these rows, then use the AutoFilter to display ONLY those rows, which could then be COPIED and PASTED to another sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks for your speedy reply Andy. Was very helpful the way you articulated this. Without knowing the source data, can see how this will help.
You are correct Data to be divided as you indicated.
Info in Data below not required.
Site Level
Location / Location Entrance level
Location / Location
Gateway / Site Entrance level

Top 2 Lines of data will simply be placed at top of the 2nd worksheet, not yet written into code.
Turbu-Lockit - Systems
19/04/2012 8:52:01 AM

 

[Andrzejek]

I took your data from the very first post, place it in Sheet1, and run this code:

Option Explicit
Dim iTo As Integer
   
Sub CopyData()
Dim iFrom As Integer
Dim strID As String
Dim strAddress As String
Dim strFloor As String
   
iTo = 1
   
For iFrom = 6 To 72
    If Range("A" & iFrom).Value <> "" Then
        If Left(Range("A" & iFrom).Value, 3) = "0E:" Then
            If Len(strID) > 0 Then
                Call PlaceInfoInSheet2(strID, strAddress, strFloor)
                strID = ""
                strAddress = ""
                strFloor = ""
            End If
            strID = Mid(Range("A" & iFrom).Value, 4)
        Else
            Select Case Range("A" & iFrom).Value
                Case "Site Level", "Location / Location Entrance level", _
                    "Entrance level", "Entrance level", _
                    "Location / Location", "Gateway / Site Entrance level"
                Case Else
                    If Len(strAddress) = 0 Then
                        strAddress = Range("A" & iFrom).Value
                    Else
                        strFloor = Range("A" & iFrom).Value
                    End If
            End Select
        End If
    End If
Next iFrom
   
End Sub
   
Sub PlaceInfoInSheet2(sID As String, sAdd As String, sFloor As String)
   
With Sheet2
    .Range("A" & iTo).Value = sID
    iTo = iTo + 1
    .Range("A" & iTo).Value = sAdd
    iTo = iTo + 1
    .Range("A" & iTo).Value = sFloor
    iTo = iTo + 1
End With
   
End Sub

In Sheet2 I've got:
[tt]
57301 Snarley
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den
57308 Bogo
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den
57309 Aardvark
123 Yarboro Blvd
Home Front
57314 Canary
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den
57328 Kithchen
123 Yarboro Blvd Home Front + Basement

57330 Bo Didily
123 Yarboro Blvd
Home Front + Basement + Sub-Basement + Yogic_Den
[/tt]

Is that what you are after?

I hard-coded start and end (from 6 to 72) to simplify, I also use all these exceptions: "Site Level", "Location / Location Entrance level", "Entrance level", "Entrance level", "Location / Location", "Gateway / Site Entrance level"


Have fun.

---- Andy

 

[Randy11]

This is awesome help Andy. In additon to helping me with current challenge, will be able to use much of this for other projects I am working at.
Only two other things I want to do with this is to leave the 0E: in front of the number (see below) & take the floor info & concatenate the 2nd & 3rd lines together. S.Be able to handle this part. (is needed for other workbook links)

If I alter the following line:
strID = Mid(Range("A" & iFrom).Value, 4)
to
strID = Mid(Range("A" & iFrom).Value, 0)
This should add the 0E: back in. Alternatively, appears I could drop a 'Text marker in front of it and get same result.

 

[PHV]

Replace this:
strID = Mid(Range("A" & iFrom).Value, 4)
with this:
strID = Range("A" & iFrom).Value

and this:
.Range("A" & iTo).Value = sAdd
iTo = iTo + 1
.Range("A" & iTo).Value = sFloor
with this:
.Range("A" & iTo).Value = sAdd & " " & sFloor

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Go with PHV's suggestion, don't use Mid if you want to get it all.

BTW, you would save yourself (and others here) a lot of trouble and time if you would start with:

This is what I have on Sheet1:...
This is what I would like to get on Sheet2:...

Just my suggestion..

Have fun.

---- Andy

 

[Randy11]

Thank you PHV & Andy Feedback & code help very much appreciated.

 

[Randy11]

Have come accross a minor snag when working with this coding.
The code above relied upon a standard that each line with an ID number on it started with OE:, this has now changed. Now all lines with an ID on them can start with any 2 digits & :
examples: could be 27: or Aa: or CC:
Is there a way to still pick up these lines and somehow apply a wildcard character in place or OE in the code line below. This does not appear to work. Assistance appreciated.

If Left(Range("A" & iFrom).Value, 3) = "OE:" Then

 

[SkipVought]

If Mid(Cells(iFrom, "A").Value, 3,1) = ":" Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

If Mid(Range("A" & iFrom).Value, 3, 1) = ":" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Randy11]

Have another minor snag surfacing relating to this code. Depending on how the original data is generated (not in my control), the following lines can be mixed in the data & need to be eliminated when moving data to page 2....
the word "Page" will start in character position 23 or 24 if afternoon when data is generated.

22/05/2012 8:52:02 AM Page 2 / 41
22/05/2012 8:52:02 AM Page 3 / 41
up to in this case Page 41 / 41
This could vary up to 100 pages or more.

Could another Mid statement locating the word "Page" be used to eliminate these lines in the data? Your feedback appreciated.

In this part of the code we are eliminating other known uneeded lines of data. Could a Mid statement somehow be added to this to located & eliminate this data from reaching Sheet2?
Else Select Case Range("A" & iFrom).Value Case "Site Level", "Location / Location Entrance level", _ "Entrance level", "Entrance level", _ "Location / Location", "Gateway / Site Entrance level"