Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Finding Characters in a String

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BobS - 11 May 2008 02:35 GMT
Novice
Excel 2003

Have a large text file (equipment log data) that I import using a macro
created by Chip Pearson (www.cpearson.com) called "ImportBigTextFile" that I
have modified slightly and it works fine.

The text files I'm working with have approx 180,000+ lines of data and the
above macro allows me to import data for 65,536 rows per sheet without
having to break up the original log file.

The imported data is tab-delimited and creates 6 columns of data (A to F)
with a header row:

"Date/Time"  "EquipDesc"  "Data-1"   "Data-2"   "Data-3"  "Data-4"

Col B (EquipDesc) which has the equipment description is a string of data
similar to this:

"Route 1 Unit 25 Southbound"

I want to be able to find the equipment Unit number (1 - 30 which are always
characters 14 & 15 in the string) in each of the rows in col B on all the
sheets and then copy and paste the entire row into a different sheet
specifically for that Unit number (30 worksheets).

For 180,000+ rows of data, that means I have 3 sheets that already have
65,536 rows of imported data that are sorted by date/time, top-to bottom.
What I need is to find all entries for each Unit number (1-30) on each sheet
then copy those entries into it's own sheet. An example of how to find text
characters within a string would be most helpful.

The workbook already has the worksheets (Unit-1, Unit-2,.....Unit-30) set up
so as I sort down thru the rows of the 3 sheets of the imported data, I
would then do a Case Select on it and then paste that row of data onto it's
corresponding sheet (Unit-1 to Unit-30).  I would include counters in the
Case Select statements to increment the row count for each sheet as each
entry is made.

Ideally, sorting the data during input and copying it to it's own worksheet
would be a better method probably but I can't figure out how to do that.
Anyone know of an example that would show that?  Since there are 30 Units,
the max row entries per worksheet would be around 6,000 rows (times 30
worksheets) and I would not need the above routine for importing more than
64K rows.

Thanks for your time, comments and suggestions.

Bob S.
Reply to this Message
Gary Keramidas - 11 May 2008 03:26 GMT
see if something like this may work. i have no idea what the name of your sheets
are.
notice the unitnum array, you need to complete the list for all possibilities.
i only have 1 - 6 and 25. the S was added to distinguish between 2 and 25 and
every other possibility.
the actual copy to sheet 2 is commented out because i don't know the name of
your destination sheets.
but step through this code and see if the autofilter actually filters by each
unit number. if it works, you should see the copy selection around the range to
be copied.

Sub test()
     Dim unitnum As Variant
     Dim rng As Range
     Dim z As Long, y As Long
     Dim lastrow As Long
     Dim ws As Worksheet, ws2 As Worksheet
     Dim fRow As Long
     Dim lRow As Long
     Set ws = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
     lastrow = Application.Min(ws.Cells(Rows.Count, "B").End(xlUp).Row, 65535)
     unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")
     Range("A1:f1") = Array("Date/Time", "EquipDesc", "Data-1", "Data-2", _
         "Data-3", "Data-4")
     y = 2
     For z = LBound(unitnum) To UBound(unitnum)
           With ws.Range("A1:f" & lastrow)
                 ' .AutoFilter Field:=2, Criteria1:="=*unit 25*",
Operator:=xlAnd
                 .AutoFilter Field:=2, Criteria1:="=*unit " & unitnum(z) & "*"

                 fRow = .CurrentRegion.Offset(1, _
                     0).SpecialCells(xlCellTypeVisible).Row
                 lRow = .Cells(Rows.Count, "B").End(xlUp).Row

                   If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
<> 1 Then
                       Set rng = ws.Range(ws.Cells(fRow, "D"), _
                           ws.Cells(Rows.Count, _
                           "D").End(xlUp)).SpecialCells(xlCellTypeVisible)
                       ws.Range("A" & fRow, "F" & lRow).Copy 'ws2.Range("A" &
y)
                       y = y + 1
                 End If
           End With
           Application.CutCopyMode = False
     Next
     ws.AutoFilterMode = False
End Sub
Signature


Gary

> Novice
> Excel 2003
[quoted text clipped - 44 lines]
>
> Bob S.
Reply to this Message
Gary Keramidas - 11 May 2008 05:12 GMT
i did some more testing. i added sheets named unit 5 and unit 25. i then
replicated "Route 1 Unit 25 Southbound" down column b on sheet1 and changed some
to Route 1 Unit 5 Southbound.

this code seemed to put them on the correct sheet, but like i mentioned, i don't
know your exact layout.

Sub test()
     Dim unitnum As Variant
     Dim rng As Range
     Dim z As Long, y As Long
     Dim lastrow As Long
     Dim ws As Worksheet, ws2 As Worksheet
     Dim fRow As Long
     Dim lRow As Long
     Dim num As Variant
     Set ws = Worksheets("Sheet1")
     lastrow = Application.Min(ws.Cells(Rows.Count, "B").End(xlUp).Row, 65535)
     unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")
     Range("A1:f1") = Array("Date/Time", "EquipDesc", "Data-1", "Data-2", _
         "Data-3", "Data-4")
     y = 2
     For z = LBound(unitnum) To UBound(unitnum)
           With ws.Range("A1:f" & lastrow)
                 .AutoFilter Field:=2, Criteria1:="=*unit " & unitnum(z) & "*"
                 fRow = .CurrentRegion.Offset(1, _
                     0).SpecialCells(xlCellTypeVisible).Row
                 lRow = .Cells(Rows.Count, "B").End(xlUp).Row

                 If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count <>
_
                     1 Then
                       num = Split(unitnum(z))
                       Set ws2 = Worksheets("unit " & num(0))
                       ws.Range("A" & fRow, "F" & lRow).Copy ws2.Range("A" & y)
                 End If
           End With
           Application.CutCopyMode = False
     Next
     ws.AutoFilterMode = False
End Sub
Signature


Gary

> Novice
> Excel 2003
[quoted text clipped - 44 lines]
>
> Bob S.
Reply to this Message
BobS - 11 May 2008 13:59 GMT
Gary,

As soon as I finish my morning "wake'em up", I'll give this a try. I
appreciate your efforts and the example code to get me going.

Thank you,

Bob S.

>i did some more testing. i added sheets named unit 5 and unit 25. i then
>replicated "Route 1 Unit 25 Southbound" down column b on sheet1 and changed
[quoted text clipped - 90 lines]
>>
>> Bob S.
Reply to this Message
Shane Devenshire - 11 May 2008 17:26 GMT
Hi Bob,

You might try something like this:

Sub DistributeData()
   Dim I As Byte
   On Error Resume Next
   For I = 1 To 30
       [A1].CurrentRegion.AutoFilter Field:=2, Criteria1:="?????????????" &
I & "*"
       Range([A2], Range("F" &
[A1].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
           Sheets("Unit-" & I).Range("A" & Sheets("Unit-" &
I).[A1].SpecialCells(xlLastCell).Row + 1)
   Next I
End Sub

You would need to modify it to work through each sheet of imported data.
This code assumes you have titles at the tops of each of the 30 output
sheets on row 1.  It also assumes that there are titles on row 1 of the data
sheets.  The code might run faster if you sort the data on the unit numbers
before you run the autofilter.   You could do that by using the Text to
Columns command to extract the 2 unit digits to column G and then sort on
that.  The On Error Resume Next command is used to handle the case where
there is no item found when the autofilter is run.

Cheers,
Shane Devenshire
Microsoft Excel MVP

> Novice
> Excel 2003
[quoted text clipped - 45 lines]
>
> Bob S.
Reply to this Message
BobS - 12 May 2008 06:07 GMT
Shane,

Your suggestion was a big help - things have morphed and I have a "bug" that will take more to explain than I'm prepared to do fully tonight. Kinda brain-dead, need a break.  This is one of those that I can't see the forest for the tree's.  Get data into all the sheets but only Unit-10 to Unit-30 sheets have all the proper data.

Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19).  Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29).  Sheet Unit-3 has only entries for unit number 30.  Sheets for Unit-4 to Unit-9 are all blank.

For Unit-10 to Unit-30 sheets - all have the correct data - not a single error.  

Played with the filter (many times). I can find entries for all the unit numbers (01 to 30) in the Unit-1 sheet.  

The log is a text file that I'm importing - no conversion of data from one format to another.

The log shows data entries as below except they show better in Wordpad and are nicely tab delimited: (downsized so it will not break - I hope).

               A                                     B                                  C                       D       E        F
-----Date/Time-------------------|------EquipDesc-----------------------------|--------Data 1----------------|Data 2|Data3|Data4|

4/26/2008 12:00:10 AM  Route 3 RTMS 30 Southbound  03(High_Speed_SB_3)  67  2367  1.0  
4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   01(Low_Speed_NB_1)  67  4118   2.0

4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   02(Middle_Speed_NB_2)  58  7696  3.0

4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   03(High_Speed_NB_3)  84 4636  2.0

4/26/2008 12:00:10 AM  Route 3 RTMS 25 Southbound   01(Low_Speed_SB_1)  66  4115 3.0

4/26/2008 12:00:10 AM  Route 3 RTMS 01 Southbound   02(Middle_Speed_SB_2)  58  6659  4.0

4/26/2008 12:00:10 AM  Route 3 RTMS 05 Southbound   03(High_Speed_SB_3)  65  3244  2.0

So the EquipDesc field is filtered using the following code:

       T is Dim'd As Byte
       UnitNum = 0

   For T = 1 To 30
       UnitNum = UnitNum + 1
       [A5].CurrentRegion.AutoFilter Field:=2, Criteria1:="Route 3 RTMS " & T & "*"
       Range([A5], Range("F" & [A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
       Sheets("Unit-" & T).Range("A" & Sheets("Unit-" & UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)
   Next T

The UnitNum is used for selecting the sheet number instead of using T.  I'm thinking there is some sort of type mis-match here that I'm not seeing and it doesn't cause an error.  From what I'm seeing it looks like the filter can't filter on a number with a leading zero - or it discards it or.....?

I did make up a another macro that sorted the data as it's being imported using Line Input and Select Case statements.  Worked great, everything went on it's respective sheet until I hit the 64K limit of records.  Even though I had far fewer than 64K rows on each sheet - that 64K limit also limits you on importing records as well as the max number of rows per sheet.

So I went back to the macro that gets all the data in and places it on 3 worksheets - a total of over 185,000 records.  Then I use the Autofilter on each of those 3 data sheets to filter/sort and copy the entries to their respective Unit numbered sheet.  Everthing works except sheets 1 to 9 as I explained above.

Scratchin head 'n butt on this one.....

Thanks for your help,

Bob S.
Reply to this Message
Gary Keramidas - 12 May 2008 15:12 GMT
"Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19).  Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29).  Sheet Unit-3 has only entries for unit number 30.  Sheets for Unit-4 to Unit-9 are all blank."

this is exactly why i did i the way i posted, so this would not happen"
unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")

Signature

Gary

 Shane,

 Your suggestion was a big help - things have morphed and I have a "bug" that will take more to explain than I'm prepared to do fully tonight. Kinda brain-dead, need a break.  This is one of those that I can't see the forest for the tree's.  Get data into all the sheets but only Unit-10 to Unit-30 sheets have all the proper data.

 Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19).  Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29).  Sheet Unit-3 has only entries for unit number 30.  Sheets for Unit-4 to Unit-9 are all blank.

 For Unit-10 to Unit-30 sheets - all have the correct data - not a single error.  

 Played with the filter (many times). I can find entries for all the unit numbers (01 to 30) in the Unit-1 sheet.  

 The log is a text file that I'm importing - no conversion of data from one format to another.

 The log shows data entries as below except they show better in Wordpad and are nicely tab delimited: (downsized so it will not break - I hope).

                 A                                     B                                  C                       D       E        F
 -----Date/Time-------------------|------EquipDesc-----------------------------|--------Data 1----------------|Data 2|Data3|Data4|

 4/26/2008 12:00:10 AM  Route 3 RTMS 30 Southbound  03(High_Speed_SB_3)  67  2367  1.0  
 4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   01(Low_Speed_NB_1)  67  4118   2.0

 4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   02(Middle_Speed_NB_2)  58  7696  3.0

 4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   03(High_Speed_NB_3)  84 4636  2.0

 4/26/2008 12:00:10 AM  Route 3 RTMS 25 Southbound   01(Low_Speed_SB_1)  66  4115 3.0

 4/26/2008 12:00:10 AM  Route 3 RTMS 01 Southbound   02(Middle_Speed_SB_2)  58  6659  4.0

 4/26/2008 12:00:10 AM  Route 3 RTMS 05 Southbound   03(High_Speed_SB_3)  65  3244  2.0

 So the EquipDesc field is filtered using the following code:

         T is Dim'd As Byte
         UnitNum = 0

     For T = 1 To 30
         UnitNum = UnitNum + 1
         [A5].CurrentRegion.AutoFilter Field:=2, Criteria1:="Route 3 RTMS " & T & "*"
         Range([A5], Range("F" & [A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
         Sheets("Unit-" & T).Range("A" & Sheets("Unit-" & UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)
     Next T

 The UnitNum is used for selecting the sheet number instead of using T.  I'm thinking there is some sort of type mis-match here that I'm not seeing and it doesn't cause an error.  From what I'm seeing it looks like the filter can't filter on a number with a leading zero - or it discards it or.....?

 I did make up a another macro that sorted the data as it's being imported using Line Input and Select Case statements.  Worked great, everything went on it's respective sheet until I hit the 64K limit of records.  Even though I had far fewer than 64K rows on each sheet - that 64K limit also limits you on importing records as well as the max number of rows per sheet.

 So I went back to the macro that gets all the data in and places it on 3 worksheets - a total of over 185,000 records.  Then I use the Autofilter on each of those 3 data sheets to filter/sort and copy the entries to their respective Unit numbered sheet.  Everthing works except sheets 1 to 9 as I explained above.

 Scratchin head 'n butt on this one.....

 Thanks for your help,

 Bob S.
Reply to this Message
BobS - 12 May 2008 20:08 GMT
Gary,

Problem solved. It's the leading zero that the filter did not like.  When I added a zero to the search string of the filter "Route 3 RTMS 0" it found everything from 01 to 09.  I did a simple If <10 check first then used this filter and the Else statement is  "Route 3 RTMS " to find the rest (10 to 30).  

I could not get your routine to work as you described but it did lead me to the conclusion that the filtering was having a problem with leading zero's.   Your example and efforts did it's job and I thank you.  I now have a spreadsheet that will import a record log of over 180,000 entries and sort them to designated Unit sheets.

Thank you,

Bob S.

 "Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19).  Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29).  Sheet Unit-3 has only entries for unit number 30.  Sheets for Unit-4 to Unit-9 are all blank."

 this is exactly why i did i the way i posted, so this would not happen"
  unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")

 --

 Gary

   "BobS" <no-spam@noplace.com> wrote in message news:%230W%23G4%23sIHA.4260@TK2MSFTNGP05.phx.gbl...
   Shane,

   Your suggestion was a big help - things have morphed and I have a "bug" that will take more to explain than I'm prepared to do fully tonight. Kinda brain-dead, need a break.  This is one of those that I can't see the forest for the tree's.  Get data into all the sheets but only Unit-10 to Unit-30 sheets have all the proper data.

   Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19).  Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29).  Sheet Unit-3 has only entries for unit number 30.  Sheets for Unit-4 to Unit-9 are all blank.

   For Unit-10 to Unit-30 sheets - all have the correct data - not a single error.  

   Played with the filter (many times). I can find entries for all the unit numbers (01 to 30) in the Unit-1 sheet.  

   The log is a text file that I'm importing - no conversion of data from one format to another.

   The log shows data entries as below except they show better in Wordpad and are nicely tab delimited: (downsized so it will not break - I hope).

                   A                                     B                                  C                       D       E        F
   -----Date/Time-------------------|------EquipDesc-----------------------------|--------Data 1----------------|Data 2|Data3|Data4|

   4/26/2008 12:00:10 AM  Route 3 RTMS 30 Southbound  03(High_Speed_SB_3)  67  2367  1.0  
   4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   01(Low_Speed_NB_1)  67  4118   2.0

   4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   02(Middle_Speed_NB_2)  58  7696  3.0

   4/26/2008 12:00:10 AM  Route 3 RTMS 03 Northbound   03(High_Speed_NB_3)  84 4636  2.0

   4/26/2008 12:00:10 AM  Route 3 RTMS 25 Southbound   01(Low_Speed_SB_1)  66  4115 3.0

   4/26/2008 12:00:10 AM  Route 3 RTMS 01 Southbound   02(Middle_Speed_SB_2)  58  6659  4.0

   4/26/2008 12:00:10 AM  Route 3 RTMS 05 Southbound   03(High_Speed_SB_3)  65  3244  2.0

   So the EquipDesc field is filtered using the following code:

           T is Dim'd As Byte
           UnitNum = 0

       For T = 1 To 30
           UnitNum = UnitNum + 1
           [A5].CurrentRegion.AutoFilter Field:=2, Criteria1:="Route 3 RTMS " & T & "*"
           Range([A5], Range("F" & [A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCellTypeVisible).Copy _
           Sheets("Unit-" & T).Range("A" & Sheets("Unit-" & UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)
       Next T

   The UnitNum is used for selecting the sheet number instead of using T.  I'm thinking there is some sort of type mis-match here that I'm not seeing and it doesn't cause an error.  From what I'm seeing it looks like the filter can't filter on a number with a leading zero - or it discards it or.....?

   I did make up a another macro that sorted the data as it's being imported using Line Input and Select Case statements.  Worked great, everything went on it's respective sheet until I hit the 64K limit of records.  Even though I had far fewer than 64K rows on each sheet - that 64K limit also limits you on importing records as well as the max number of rows per sheet.

   So I went back to the macro that gets all the data in and places it on 3 worksheets - a total of over 185,000 records.  Then I use the Autofilter on each of those 3 data sheets to filter/sort and copy the entries to their respective Unit numbered sheet.  Everthing works except sheets 1 to 9 as I explained above.

   Scratchin head 'n butt on this one.....

   Thanks for your help,

   Bob S.
Reply to this Message
BobS - 12 May 2008 20:16 GMT
Shane,

Found that the filter had a problem with leading zero's on the record
entries (i.e. 01, 02....09).  See my reply to Gary for further explanation.

A big Thank you to you and Gary both for your assistance.  Your examples
gave me the tutoring I needed to get me headed the right way.  A long
weekend, several hours of researching my old MS Excel Language Reference
guides and O'Reilly's "VB & VBA In a Nutshell" books and I'm now smarter
than the average mole.....again....;-)

Talk about "use it or lose it" ! Not that I was ever an expert on VB or VBA
but these day's I have to look at my nametag in the mornings....

Thank you - your help was very much appreciated.

Bob S.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.