ExcelBanter - vb code to re-arrange data

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - vb code to re-arrange data (https://www.excelbanter.com/excel-programming/403893-vbulletin-code-re-arrange-data.html)

Svenman

vb code to re-arrange data

joel

vb code to re-arrange data

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?

"Svenman" wrote:

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

Svenman

vb code to re-arrange data

On Jan 9, 7:45*am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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

joel

vb code to re-arrange data

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

"Svenman" wrote:

On Jan 9, 7:45 am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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

Svenman

vb code to re-arrange data

On Jan 9, 12:27*pm, Joel wrote:
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

"Svenman" wrote:
On Jan 9, 7:45 am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

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

joel

vb code to re-arrange data

Svenman

vb code to re-arrange data

On Jan 9, 1:58*pm, Joel wrote:
The RowCount need to be change from 1 to some other number.

You can email me at

"Svenman" wrote:
On Jan 9, 12:27 pm, Joel wrote:
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

"Svenman" wrote:
On Jan 9, 7:45 am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

Joel,

I think I have a bad email address for you; mail was rejected to
' and '

Sven

joel

vb code to re-arrange data

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 @.

"Svenman" wrote:

On Jan 9, 1:58 pm, Joel wrote:
The RowCount need to be change from 1 to some other number.

You can email me at

"Svenman" wrote:
On Jan 9, 12:27 pm, Joel wrote:
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

"Svenman" wrote:
On Jan 9, 7:45 am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

Joel,

I think I have a bad email address for you; mail was rejected to
' and '

Sven

Svenman

vb code to re-arrange data

On Jan 9, 5:29 pm, Joel wrote:
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 @.

"Svenman" wrote:
On Jan 9, 1:58 pm, Joel wrote:
The RowCount need to be change from 1 to some other number.

You can email me at

"Svenman" wrote:
On Jan 9, 12:27 pm, Joel wrote:
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

"Svenman" wrote:
On Jan 9, 7:45 am, Joel wrote:
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?

"Svenman" wrote:
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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

Joel,

I think I have a bad email address for you; mail was rejected to
' and '

Sven

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

Svenman

vb code to re-arrange data

INTP56

vb code to re-arrange data

All times are GMT +1. The time now is 05:58 PM.