software

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.

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 quot;Twoquot;, 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 quot;Twoquot; 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(quot;B2quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
With Sheets(quot;Twoquot;)
Set Dest = .Range(quot;B2quot;)
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
quot;Jim J.quot; gt; wrote in message
news
gt; Is there some way to ignore data in certain rows, while extracting data
gt; from
gt; others, while not leaving any empty rows at the extracted level? Here's
gt; what
gt; I want to do:
gt; Worksheet #1 has data in rows (which I copy and paste as quot;text stringsquot;
gt; from
gt; a report, because the quot;Text Import Wizardquot; in Excel can't deal with all
gt; the
gt; clutter from the original report). A partial example of the report data
gt; would be as follows:
gt; 05/05/2006 873318 0030 Rlsd PART102-002 03/09
gt; Cbl 10/2 Cable Blk 1
gt; Totals: 5.25
gt; 0.5
gt;
gt; 05/16/2006 010796 0020 Plnd PART109C-002 04/18
gt; Cbl 10/1 Cable 80c B
gt; Totals:
gt; 0.3333 1.0
gt;
gt; 05/17/2006 990778 0020 Rlsd PART284-008 05/09
gt; Cbl 28/4 Cable 75c
gt; Blk 7X
gt; 988467 0020 Strt PART284-002 05/08
gt; Cbl 28/4 M Cable 10
gt; 003039 0020 Strt PART223-032 05/09
gt; Cbl 22/3 105C YE
gt; 978250 0030 Strt PART265-001 05/08
gt; Cbl 26/5 M Cable 80
gt; Totals:
gt; 7.3333 2.6
gt; The only rows that I want to extract data from are ones that have a
gt; 6-digit
gt; Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has
gt; an
gt; Order Number, I will extract data from that row, as well as the following
gt; row. If a row is blank, or has a quot;Totals:quot; entry, I want to ignore those
gt; rows completely. So once I extract the data to Worksheet #2, it should
gt; look
gt; like this:
gt; 873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
gt; 010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
gt; 990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
gt; 988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
gt; 003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
gt; 978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
gt; So, in the end, out of the 17 rows that I started with, I end up with only
gt; 6
gt; rows.
gt; PS By the way, I know how to extract the data for the various columns from
gt; the various text strings, so that isn't an issue.
Otto,
This might work, but I have insufficient quot;macroquot; 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.

quot;Otto Moehrbachquot; wrote:

gt; The following macro will do that for you. I assumed your Order Numbers are
gt; in Column B and each subsequent entry in subsequent columns, ending with
gt; Column F.
gt; This macro will look at each cell in Column B. When it finds an entry that
gt; is 6 characters long, it will copy Columns B:F of that row and paste it into
gt; a sheet named quot;Twoquot;, starting in row 2, in Column B. It will then take the
gt; entry (in the first sheet) in the next row in Column F and copy that cell
gt; and paste it to the same row in sheet quot;Twoquot; in Column G.
gt; It will then look at the next cell in Column B of the first sheet. Post
gt; back if this doesn't do what you want. HTH Otto
gt; Sub ShuffleData()
gt; Dim RngB As Range
gt; Dim Dest As Range
gt; Dim i As Range
gt; Set RngB = Range(quot;B2quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
gt; With Sheets(quot;Twoquot;)
gt; Set Dest = .Range(quot;B2quot;)
gt; For Each i In RngB
gt; If Len(i.Value) = 6 Then
gt; i.Resize(, 5).Copy Dest
gt; i.Offset(1, 4).Copy Dest.Offset(, 5)
gt; Set Dest = Dest.Offset(1)
gt; End If
gt; Next i
gt; End With
gt; End Sub
gt; quot;Jim J.quot; gt; wrote in message
gt; news
gt; gt; Is there some way to ignore data in certain rows, while extracting data
gt; gt; from
gt; gt; others, while not leaving any empty rows at the extracted level? Here's
gt; gt; what
gt; gt; I want to do:
gt; gt; Worksheet #1 has data in rows (which I copy and paste as quot;text stringsquot;
gt; gt; from
gt; gt; a report, because the quot;Text Import Wizardquot; in Excel can't deal with all
gt; gt; the
gt; gt; clutter from the original report). A partial example of the report data
gt; gt; would be as follows:
gt; gt; 05/05/2006 873318 0030 Rlsd PART102-002 03/09
gt; gt; Cbl 10/2 Cable Blk 1
gt; gt; Totals: 5.25
gt; gt; 0.5
gt; gt;
gt; gt; 05/16/2006 010796 0020 Plnd PART109C-002 04/18
gt; gt; Cbl 10/1 Cable 80c B
gt; gt; Totals:
gt; gt; 0.3333 1.0
gt; gt;
gt; gt; 05/17/2006 990778 0020 Rlsd PART284-008 05/09
gt; gt; Cbl 28/4 Cable 75c
gt; gt; Blk 7X
gt; gt; 988467 0020 Strt PART284-002 05/08
gt; gt; Cbl 28/4 M Cable 10
gt; gt; 003039 0020 Strt PART223-032 05/09
gt; gt; Cbl 22/3 105C YE
gt; gt; 978250 0030 Strt PART265-001 05/08
gt; gt; Cbl 26/5 M Cable 80
gt; gt; Totals:
gt; gt; 7.3333 2.6
gt; gt; The only rows that I want to extract data from are ones that have a
gt; gt; 6-digit
gt; gt; Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has
gt; gt; an
gt; gt; Order Number, I will extract data from that row, as well as the following
gt; gt; row. If a row is blank, or has a quot;Totals:quot; entry, I want to ignore those
gt; gt; rows completely. So once I extract the data to Worksheet #2, it should
gt; gt; look
gt; gt; like this:
gt; gt; 873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
gt; gt; 010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
gt; gt; 990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
gt; gt; 988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
gt; gt; 003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
gt; gt; 978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
gt; gt; So, in the end, out of the 17 rows that I started with, I end up with only
gt; gt; 6
gt; gt; rows.
gt; gt; PS By the way, I know how to extract the data for the various columns from
gt; gt; the various text strings, so that isn't an issue.
gt;
gt;
gt;