Ignoring Rows When Extracting Data From One Worksheet To Another
 

Is there some way to ignore data in certain rows, while extracting data from
others, while not leaving any empty rows at the extracted level? Heres what
I want to do:
Worksheet #1 has data in rows (which I copy and paste as €œtext strings€� from
a report, because the €œText Import Wizard€� in Excel cant deal with all the
clutter from the original report). A partial example of the report data
would be as follows:
05/05/2006 873318 0030 Rlsd PART102-002 03/09
Cbl 10/2 Cable Blk 1
Totals: 5.25
0.5

05/16/2006 010796 0020 Plnd PART109C-002 04/18
Cbl 10/1 Cable 80c B
Totals:
0.3333 1.0

05/17/2006 990778 0020 Rlsd PART284-008 05/09
Cbl 28/4 Cable 75c
Blk 7X
988467 0020 Strt PART284-002 05/08
Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09
Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08
Cbl 26/5 M Cable 80
Totals:
7.3333 2.6
The only rows that I want to extract data from are ones that have a 6-digit
Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has an
Order Number, I will extract data from that row, as well as the following
row. If a row is blank, or has a €œTotals:€� entry, I want to ignore those
rows completely. So once I extract the data to Worksheet #2, it should look
like this:
873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
So, in the end, out of the 17 rows that I started with, I end up with only 6
rows.
PS By the way, I know how to extract the data for the various columns from
the various text strings, so that isnt an issue.

Ignoring Rows When Extracting Data From One Worksheet To Another
 

The following macro will do that for you. I assumed your Order Numbers are
in Column B and each subsequent entry in subsequent columns, ending with
Column F.
This macro will look at each cell in Column B. When it finds an entry that
is 6 characters long, it will copy Columns B:F of that row and paste it into
a sheet named "Two", starting in row 2, in Column B. It will then take the
entry (in the first sheet) in the next row in Column F and copy that cell
and paste it to the same row in sheet "Two" in Column G.
It will then look at the next cell in Column B of the first sheet. Post
back if this doesn't do what you want. HTH Otto
Sub ShuffleData()
Dim RngB As Range
Dim Dest As Range
Dim i As Range
Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
With Sheets("Two")
Set Dest = .Range("B2")
For Each i In RngB
If Len(i.Value) = 6 Then
i.Resize(, 5).Copy Dest
i.Offset(1, 4).Copy Dest.Offset(, 5)
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Jim J." wrote in message
...
Is there some way to ignore data in certain rows, while extracting data
from
others, while not leaving any empty rows at the extracted level? Here's
what
I want to do:
Worksheet #1 has data in rows (which I copy and paste as "text strings"
from
a report, because the "Text Import Wizard" in Excel can't deal with all
the
clutter from the original report). A partial example of the report data
would be as follows:
05/05/2006 873318 0030 Rlsd PART102-002 03/09
Cbl 10/2 Cable Blk 1
Totals: 5.25
0.5

05/16/2006 010796 0020 Plnd PART109C-002 04/18
Cbl 10/1 Cable 80c B
Totals:
0.3333 1.0

05/17/2006 990778 0020 Rlsd PART284-008 05/09
Cbl 28/4 Cable 75c
Blk 7X
988467 0020 Strt PART284-002 05/08
Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09
Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08
Cbl 26/5 M Cable 80
Totals:
7.3333 2.6
The only rows that I want to extract data from are ones that have a
6-digit
Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has
an
Order Number, I will extract data from that row, as well as the following
row. If a row is blank, or has a "Totals:" entry, I want to ignore those
rows completely. So once I extract the data to Worksheet #2, it should
look
like this:
873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
So, in the end, out of the 17 rows that I started with, I end up with only
6
rows.
PS By the way, I know how to extract the data for the various columns from
the various text strings, so that isn't an issue.

Ignoring Rows When Extracting Data From One Worksheet To Anoth
 

Otto,
This might work, but I have insufficient "macro" knowledge to try this out.
A suggestion, please, regarding how I could learn more about macros so I can
try this out.
Thanks,
Jim J.

"Otto Moehrbach" wrote:

The following macro will do that for you. I assumed your Order Numbers are
in Column B and each subsequent entry in subsequent columns, ending with
Column F.
This macro will look at each cell in Column B. When it finds an entry that
is 6 characters long, it will copy Columns B:F of that row and paste it into
a sheet named "Two", starting in row 2, in Column B. It will then take the
entry (in the first sheet) in the next row in Column F and copy that cell
and paste it to the same row in sheet "Two" in Column G.
It will then look at the next cell in Column B of the first sheet. Post
back if this doesn't do what you want. HTH Otto
Sub ShuffleData()
Dim RngB As Range
Dim Dest As Range
Dim i As Range
Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
With Sheets("Two")
Set Dest = .Range("B2")
For Each i In RngB
If Len(i.Value) = 6 Then
i.Resize(, 5).Copy Dest
i.Offset(1, 4).Copy Dest.Offset(, 5)
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Jim J." wrote in message
...
Is there some way to ignore data in certain rows, while extracting data
from
others, while not leaving any empty rows at the extracted level? Here's
what
I want to do:
Worksheet #1 has data in rows (which I copy and paste as "text strings"
from
a report, because the "Text Import Wizard" in Excel can't deal with all
the
clutter from the original report). A partial example of the report data
would be as follows:
05/05/2006 873318 0030 Rlsd PART102-002 03/09
Cbl 10/2 Cable Blk 1
Totals: 5.25
0.5

05/16/2006 010796 0020 Plnd PART109C-002 04/18
Cbl 10/1 Cable 80c B
Totals:
0.3333 1.0

05/17/2006 990778 0020 Rlsd PART284-008 05/09
Cbl 28/4 Cable 75c
Blk 7X
988467 0020 Strt PART284-002 05/08
Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09
Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08
Cbl 26/5 M Cable 80
Totals:
7.3333 2.6
The only rows that I want to extract data from are ones that have a
6-digit
Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has
an
Order Number, I will extract data from that row, as well as the following
row. If a row is blank, or has a "Totals:" entry, I want to ignore those
rows completely. So once I extract the data to Worksheet #2, it should
look
like this:
873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
So, in the end, out of the 17 rows that I started with, I end up with only
6
rows.
PS By the way, I know how to extract the data for the various columns from
the various text strings, so that isn't an issue.