vb code to re-arrange data

S

Svenman

Hello!

I have an unusual looking Excel spreadsheet that is produced by our IT
department. I am looking for some help as how to modify the layout in
anticipation to an export to Access.

The layout of the Excel spreadsheet is listed below. I am using the
pipe character to indicate that the data is in the next cell.
However, the address information (lines 2,3,4) is not separated across
different cells other than by separate rows. Also note that there is
a string of dashes serving as a sort of record delimiter:


Customer Number(n) | Salesman | Date| LOB | Region
William Brown|
123 Main Street|
Rockledge FL 32955|

------------------------------------------------------------------------------------------------------------
Customer Number(n+1) | Salesman | Date| LOB | Region
Ernie Simon|
541 W. Main Street|
Suite 231|
Cocoa Beach FL 32912|
------------------------------------------------------------------------------------------------------------
Customer Number(n+2) | Salesman | Date| LOB | Region
Dave Johnson|
552 Harbor Drive|
Port Richey FL 32119-9818|

I would like to restructure the data so that each "line" holds a
complete record, and do away with the dashed line record delimiter.
Basically, this is what I would like to create:

CustNumber(n) | Salesman | Date| LOB | Region | CustName | Address1 |
Address2 | CityStateZip
CustNumber(n+1) | Salesman | Date| LOB | Region | CustName | Address1
| Address2 | CityStateZip
CustNumber(n+2) | Salesman | Date| LOB | Region | CustName | Address1
| Address2 | CityStateZip

Can anyone suggest some quick code to make this happen?

Thanks in advance,

Sven Asnien
 
J

Joel

This task may be simple or very complex, I need more info. Also a sample of
a couple of line from the spreadsheet.


The problem is with the number of line in the address. I can't tell from
you posting how to handle the optional address2 such as "suite 231". Is
there a blank column for the optional address2. Is every entry two rows and
we are just taking the second row and putting into the first empty column on
the first row? Are we skipping columns for any special casses if data is
missing?
 
S

Svenman

This task may be simple or very complex, I need more info.  Also a sample of
a couple of line from the spreadsheet.

The problem is with the number of line in the address.  I can't tell from
you posting how to handle the optional address2 such as "suite 231".  Is
there a blank column for the optional address2.  Is every entry two rowsand
we are just taking the second row and putting into the first empty column on
the first row?  Are we skipping columns for any special casses if data is
missing?














- Show quoted text -
Click to expand...

Joel -
There is the possibility that Address2 may not be populated. I am not
so concerned about leaving a blank column for address 2. For my
purposes, I could just as well call my columns
'Line1','Line2','Line3','Line4','Line5'. I do not need to skip
columns if data is missing. I plan to display the information in a
subform to the salesperson; from there the salesperson will
distinguish zipcode, Suite Number, etc.

I am hoping that I can use an if/then statement to take each row below
the custNumber(n) line through the ---------------- delimiter row and
append a new column of data to the respective custNumber(n) line

Basically, I need to append each 'Line' to the end of the proper
CustNumber(n) | Salesman | Date| LOB | Region | row.

Like such: (I am using the | symbol to indicate a new column in Excel)
10220|Billy W.|01/01/2008|Steel |East|William Brown|123 Main Street|
Rockledge FL 32955
10221|Jim T.|01/01/2008|Copper |North|Ernie Simon|541 W. Main Street|
Suite 231|Cocoa Beach FL 32912
10222|Pat S.|01/03/2008|Gold |West|Dave Johnson|552 Harbor Drive|Port
Richey FL 32119-9818

I hope that is clearer,

Svenman
 
J

Joel

see if this code works

Sub combinerows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1
SecondLine = Range("A" & (RowCount + 1))
Rows(RowCount + 1).Delete
Do While SecondLine <> ""
'strip off leading -
Do While Left(SecondLine, 1) = "-"
SecondLine = Mid(SecondLine, 2)
Loop
NewData = ""
Do While Len(SecondLine) > 0
If Left(SecondLine, 1) = "-" Then Exit Do

NewData = NewData & Left(SecondLine, 1)
SecondLine = Mid(SecondLine, 2)

Loop
If NewData <> "" Then
Cells(RowCount, NewCol) = NewData
NewCol = NewCol + 1
End If
Loop
RowCount = RowCount + 1
Loop
End Sub
 
S

Svenman

see if this code works

Sub combinerows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
   LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
   NewCol = LastCol + 1
   SecondLine = Range("A" & (RowCount + 1))
   Rows(RowCount + 1).Delete
   Do While SecondLine <> ""
      'strip off leading -
      Do While Left(SecondLine, 1) = "-"
         SecondLine = Mid(SecondLine, 2)
      Loop
      NewData = ""
      Do While Len(SecondLine) > 0
         If Left(SecondLine, 1) = "-" Then Exit Do

         NewData = NewData & Left(SecondLine, 1)
         SecondLine = Mid(SecondLine, 2)

      Loop
      If NewData <> "" Then
         Cells(RowCount, NewCol) = NewData
         NewCol = NewCol + 1
      End If
   Loop
   RowCount = RowCount + 1
Loop
End Sub











- Show quoted text -
Click to expand...

Sorry Joel,

It did not seem to run as expected; it combined addresses from record
details from different records, and deleted the detail information.

Is there some way I can attach/post the spreadsheet results for your
review, or can I send them directly to you for review?

Thanks,

Svenman
 
J

Joel

The RowCount need to be change from 1 to some other number.

You can email me at (e-mail address removed)
 
S

Svenman

The RowCount need to be change from 1 to some other number.

You can email me at (e-mail address removed)








- Show quoted text -
Click to expand...

Joel,

I think I have a bad email address for you; mail was rejected to
'(e-mail address removed)' and '(e-mail address removed)'

Sven
 
J

Joel

Microsoft has some anti-spam software that sometimes modifies email addresses.

joel dot warburg at itt dot com

remove spaces and replace dot with a period and the at with @.
 
S

Svenman

Microsoft has some anti-spam software that sometimes modifies email addresses.

joel dot warburg at itt dot com

remove spaces and replace dot with a period and the at with @.
Click to expand...

Thanks Joel,

I just sent to your email address, and I have not yet had a return to
sender for a bad email address. I am looking forward to your
suggestion.

Svenman
 
S

Svenman

Microsoft has some anti-spam software that sometimes modifies email addresses.

joel dot warburg at itt dot com

remove spaces and replace dot with a period and the at with @.
Click to expand...

Thanks Joel,

I just sent to your email address, and I have not yet had a return to
sender for a bad email address. I am looking forward to your
suggestion.

Svenman
 
I

INTP56

Is it possible to have the IT department create a view for you that outputs
the data as a table? It seems you are starting with a file intended for
people when you need a file intended for programs. Or at least outputs the
data in a simple tab delimited file that can be read into Excel without
having manipulate the data.

Bob
 
Top