software

I've been struggling with this one for a day. The following data is is in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or more
spaces to the row right above it and tacked on to the data in that row and
then remove 20 of the spaces off the beginning of the data that was moved .
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

If the data to be moved has anywhere from 3 to 19 leading spaces, how can 20
leading spaces be removed? Or are you saying that if it has as much as 3,
then it must have 20 leading spaces? Please post back and clarify. HTH
Otto
quot;Dennisquot; gt; wrote in message
. com...
gt; I've been struggling with this one for a day. The following data is is in
gt; Column A.
gt;
gt; (GOLD)JOB/402/ATTBYE
gt; DBDATA 30450 30
gt;
gt; What I'm trying to to accomplish is to move data that starts with 3 or
gt; more
gt; spaces to the row right above it and tacked on to the data in that row and
gt; then remove 20 of the spaces off the beginning of the data that was moved
gt; .
gt; After this is done the following would be the result and in column A....
gt;
gt; (GOLD)JOB/402/ATTBY DBDATA 30450 3
gt;
gt; I have about 20,000 rows of data to go thru and have processed if it finds
gt; data that starts with more than 3 spaces in Column A.
gt;
gt; TIA....Dennis
gt; ===================
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
Yes Otto, the rows that have at last 3 leading spaces will always have more
than 20 leading spaces. Thanx and TIA......Dennis

In article gt;, quot;Otto Moehrbachquot;
gt; wrote:
gt;If the data to be moved has anywhere from 3 to 19 leading spaces, how can 20
gt;leading spaces be removed? Or are you saying that if it has as much as 3,
gt;then it must have 20 leading spaces? Please post back and clarify. HTH
gt;Otto
gt;quot;Dennisquot; gt; wrote in message .com...
gt;gt; I've been struggling with this one for a day. The following data is is in
gt;gt; Column A.
gt;gt;
gt;gt; (GOLD)JOB/402/ATTBYE
gt;gt; DBDATA 30450 30
gt;gt;
gt;gt; What I'm trying to to accomplish is to move data that starts with 3 or
gt;gt; more
gt;gt; spaces to the row right above it and tacked on to the data in that row and
gt;gt; then remove 20 of the spaces off the beginning of the data that was moved
gt;gt; .
gt;gt; After this is done the following would be the result and in column A....
gt;gt;
gt;gt; (GOLD)JOB/402/ATTBY DBDATA 30450 3
gt;gt;
gt;gt; I have about 20,000 rows of data to go thru and have processed if it finds
gt;gt; data that starts with more than 3 spaces in Column A.
gt;gt;
gt;gt; TIA....Dennis
gt;gt; ===================
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;

Dennis
The following macro will do what you asked for.
As written, this macro will work on the active sheet.
I assumed that your data is in Column A starting with A2. I assumed A1 is a
header.
Note that there is no error trapping in this macro. You may need or want
some error trapping. For instance, as written, this macro checks for an
entry in Column A that has 3 leading spaces. That entry, when found, will
then be changed by removing the first 20 characters (a space is a
character). What's left will then be appended to the contents of the cell
above it. Obviously, if the entry has less that 20 leading spaces, you will
lose some data.
Note that the entire row of any entry found to have 3 leading spaces will be
deleted by this macro after the contents are appended to the cell above it.
I highly recommend that you make a copy of your file and run this macro in
that copy first.
I am sending you a small file that has this macro properly placed and
working. I am sending this to the email address that is attached to your
post. If this is not a valid email address, email me and give me a valid
email address if you want this file. My email address is
. Remove the quot;nopquot; from this address. HTH Otto
Sub MoveData()
Dim RngA As Range 'Range of Col A from A2 down
Dim c As Long
Dim Spaces3 As String 'Entry with gt;=3 leading spaces -20 leading
characters
Set RngA = Range(quot;A2quot;, Range(quot;Aquot; amp; Rows.Count).End(xlUp))
For c = RngA.Count - 1 To 1 Step -1
If Left(RngA(c).Offset(1), 3) = quot; quot; Then
Spaces3 = Right(RngA(c).Offset(1), Len(RngA(c).Offset(1)) - 20)
RngA(c).Value = RngA(c).Value amp; Spaces3
RngA(c).Offset(1).EntireRow.Delete
End If
Next c
End Sub
quot;Dennisquot; gt; wrote in message
. com...
gt; Yes Otto, the rows that have at last 3 leading spaces will always have
gt; more
gt; than 20 leading spaces. Thanx and TIA......Dennis
gt;
gt; In article gt;, quot;Otto Moehrbachquot;
gt; gt; wrote:
gt;gt;If the data to be moved has anywhere from 3 to 19 leading spaces, how can
gt;gt;20
gt;gt;leading spaces be removed? Or are you saying that if it has as much as 3,
gt;gt;then it must have 20 leading spaces? Please post back and clarify. HTH
gt;gt;Otto
gt;gt;quot;Dennisquot; gt; wrote in message y.com...
gt;gt;gt; I've been struggling with this one for a day. The following data is is
gt;gt;gt; in
gt;gt;gt; Column A.
gt;gt;gt;
gt;gt;gt; (GOLD)JOB/402/ATTBYE
gt;gt;gt; DBDATA 30450 30
gt;gt;gt;
gt;gt;gt; What I'm trying to to accomplish is to move data that starts with 3 or
gt;gt;gt; more
gt;gt;gt; spaces to the row right above it and tacked on to the data in that row
gt;gt;gt; and
gt;gt;gt; then remove 20 of the spaces off the beginning of the data that was
gt;gt;gt; moved
gt;gt;gt; .
gt;gt;gt; After this is done the following would be the result and in column A....
gt;gt;gt;
gt;gt;gt; (GOLD)JOB/402/ATTBY DBDATA 30450 3
gt;gt;gt;
gt;gt;gt; I have about 20,000 rows of data to go thru and have processed if it
gt;gt;gt; finds
gt;gt;gt; data that starts with more than 3 spaces in Column A.
gt;gt;gt;
gt;gt;gt; TIA....Dennis
gt;gt;gt; ===================
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
Dennis
The email address in your post is not a valid email address. Otto
quot;Dennisquot; gt; wrote in message
. com...
gt; Yes Otto, the rows that have at last 3 leading spaces will always have
gt; more
gt; than 20 leading spaces. Thanx and TIA......Dennis
gt;
gt; In article gt;, quot;Otto Moehrbachquot;
gt; gt; wrote:
gt;gt;If the data to be moved has anywhere from 3 to 19 leading spaces, how can
gt;gt;20
gt;gt;leading spaces be removed? Or are you saying that if it has as much as 3,
gt;gt;then it must have 20 leading spaces? Please post back and clarify. HTH
gt;gt;Otto
gt;gt;quot;Dennisquot; gt; wrote in message y.com...
gt;gt;gt; I've been struggling with this one for a day. The following data is is
gt;gt;gt; in
gt;gt;gt; Column A.
gt;gt;gt;
gt;gt;gt; (GOLD)JOB/402/ATTBYE
gt;gt;gt; DBDATA 30450 30
gt;gt;gt;
gt;gt;gt; What I'm trying to to accomplish is to move data that starts with 3 or
gt;gt;gt; more
gt;gt;gt; spaces to the row right above it and tacked on to the data in that row
gt;gt;gt; and
gt;gt;gt; then remove 20 of the spaces off the beginning of the data that was
gt;gt;gt; moved
gt;gt;gt; .
gt;gt;gt; After this is done the following would be the result and in column A....
gt;gt;gt;
gt;gt;gt; (GOLD)JOB/402/ATTBY DBDATA 30450 3
gt;gt;gt;
gt;gt;gt; I have about 20,000 rows of data to go thru and have processed if it
gt;gt;gt; finds
gt;gt;gt; data that starts with more than 3 spaces in Column A.
gt;gt;gt;
gt;gt;gt; TIA....Dennis
gt;gt;gt; ===================
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
Otto...thank you!!!!!!!!!!!!!!!! This did exactly what I was looking for and
written very lean. Thanx again!!!

Fixed my email

Dennis
=================

In article gt;, quot;Otto Moehrbachquot;
gt; wrote:

gt;Sub MoveData()
gt; Dim RngA As Range 'Range of Col A from A2 down
gt; Dim c As Long
gt; Dim Spaces3 As String 'Entry with gt;=3 leading spaces -20 leading
gt;characters
gt; Set RngA = Range(quot;A2quot;, Range(quot;Aquot; amp; Rows.Count).End(xlUp))
gt; For c = RngA.Count - 1 To 1 Step -1
gt; If Left(RngA(c).Offset(1), 3) = quot; quot; Then
gt; Spaces3 = Right(RngA(c).Offset(1), Len(RngA(c).Offset(1)) - 20)
gt; RngA(c).Value = RngA(c).Value amp; Spaces3
gt; RngA(c).Offset(1).EntireRow.Delete
gt; End If
gt; Next c
gt;End Sub

Dennis
You should never display your real email address in a newsgroup posting.
The spammers use computers to quot;harvestquot; email addresses from newsgroups by
the thousands and then send junk for ever. You should display a fake
address as I do and then include an explanation for someone you want an
email from as to how to convert the fake to a real email address. Because
the spammers use machines, they don't pick up on the explanation. HTH
Otto
quot;Otto Moehrbachquot; gt; wrote in message
...
gt; Dennis
gt; The email address in your post is not a valid email address. Otto
gt; quot;Dennisquot; gt; wrote in message
gt; . com...
gt;gt; Yes Otto, the rows that have at last 3 leading spaces will always have
gt;gt; more
gt;gt; than 20 leading spaces. Thanx and TIA......Dennis
gt;gt;
gt;gt; In article gt;, quot;Otto Moehrbachquot;
gt;gt; gt; wrote:
gt;gt;gt;If the data to be moved has anywhere from 3 to 19 leading spaces, how can
gt;gt;gt;20
gt;gt;gt;leading spaces be removed? Or are you saying that if it has as much as
gt;gt;gt;3,
gt;gt;gt;then it must have 20 leading spaces? Please post back and clarify.
gt;gt;gt;HTH
gt;gt;gt;Otto
gt;gt;gt;quot;Dennisquot; gt; wrote in message gy.com...
gt;gt;gt;gt; I've been struggling with this one for a day. The following data is is
gt;gt;gt;gt; in
gt;gt;gt;gt; Column A.
gt;gt;gt;gt;
gt;gt;gt;gt; (GOLD)JOB/402/ATTBYE
gt;gt;gt;gt; DBDATA 30450 30
gt;gt;gt;gt;
gt;gt;gt;gt; What I'm trying to to accomplish is to move data that starts with 3 or
gt;gt;gt;gt; more
gt;gt;gt;gt; spaces to the row right above it and tacked on to the data in that row
gt;gt;gt;gt; and
gt;gt;gt;gt; then remove 20 of the spaces off the beginning of the data that was
gt;gt;gt;gt; moved
gt;gt;gt;gt; .
gt;gt;gt;gt; After this is done the following would be the result and in column
gt;gt;gt;gt; A....
gt;gt;gt;gt;
gt;gt;gt;gt; (GOLD)JOB/402/ATTBY DBDATA 30450 3
gt;gt;gt;gt;
gt;gt;gt;gt; I have about 20,000 rows of data to go thru and have processed if it
gt;gt;gt;gt; finds
gt;gt;gt;gt; data that starts with more than 3 spaces in Column A.
gt;gt;gt;gt;
gt;gt;gt;gt; TIA....Dennis
gt;gt;gt;gt; ===================
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;