I've got an excel spreadsheet that is sent to me every 2 - 3 days, with
multiple columns of data in it. For the most part, it will always follow the
same format from left to right. Occassionally, one of the columns of data
sent to me will get mixed up with another one when the sender exports it
from their database. I've spoke to them on numerous occassions about this,
and they insist that the data is that way in their database, so "...it's out
of our control..."

*hangs head*

So now I'm left cleaning up someone else's mess.

There's a good side to this though. I've found a consistent pattern to the
difference between the regular and different formatting.

The spreadsheet has user's first, middle, last name, SSN#, company name,
address1, address2, address3, city, state, zip, phone, fax, email, and some
other fields mixed in there.

The problem comes when the sender's data export gets their client's
"address1" field mixed up with their "company name". When this happens,
"company name, address1, and address2" all shift right one field. Address3
is otherwise NEVER used. So imy question is this.

Is there a way to check for data in a field in a row, then take that field,
and the 2 previous fields, and cut / paste said 3 fields to the left one.
But all other fields of data within that row must be left alone.

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| | Bob's trucks | 45th avenue |#13
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

it should look like

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| Bob's trucks | 45th avenue |#13 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

Is there SOME way to automate this? I'm getting 1000+ addresses a week, and
I can't keep doing this.

--


Brad S. Russell

Certainly. You just need to check to see if the Address 3 field is
empty. If it isn't, move the data with in it and within the previous
2 cells over. Something like this should work. Of course, not
knowing your specific column letters, I can't write exactly what you
would use, but you should get the idea.
Also, there is certainly a better way to do the move, but I'm not at a
computer that has Excel on it, so this is just off the top of my head.
This is assuming that Address 3 is in column D
Sub movestuff()
Dim colNum As Integer
Dim rowNum As Long
colNum = 4
BotRow = Cells(65536, colNum).End(xlUp).Row
For rowNum = 2 To BotRow
If Not IsEmpty(Cells(rowNum, colNum)) Then
For m = 2 To colNum
Cells(rowNum, m).Cut Destination:=Cells(rowNum, m - 1)
Next m
End If
Next rowNum
End Sub


Blaster Master wrote:
I've got an excel spreadsheet that is sent to me every 2 - 3 days, with
multiple columns of data in it. For the most part, it will always follow the
same format from left to right. Occassionally, one of the columns of data
sent to me will get mixed up with another one when the sender exports it
from their database. I've spoke to them on numerous occassions about this,
and they insist that the data is that way in their database, so "...it's out
of our control..."

*hangs head*

So now I'm left cleaning up someone else's mess.

There's a good side to this though. I've found a consistent pattern to the
difference between the regular and different formatting.

The spreadsheet has user's first, middle, last name, SSN#, company name,
address1, address2, address3, city, state, zip, phone, fax, email, and some
other fields mixed in there.

The problem comes when the sender's data export gets their client's
"address1" field mixed up with their "company name". When this happens,
"company name, address1, and address2" all shift right one field. Address3
is otherwise NEVER used. So imy question is this.

Is there a way to check for data in a field in a row, then take that field,
and the 2 previous fields, and cut / paste said 3 fields to the left one.
But all other fields of data within that row must be left alone.

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| | Bob's trucks | 45th avenue |#13
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

it should look like

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| Bob's trucks | 45th avenue |#13 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

Is there SOME way to automate this? I'm getting 1000+ addresses a week, and
I can't keep doing this.

--


Brad S. Russell

Try this. Looks for empty cell in column A, then moves columns B - D to
columns A-C


Sub correctinputdata()

Lastrow = Cells(Rows.Count, "A").End(xlUp)

For RowCount = 1 To Lastrow

If IsEmpty(Cells(RowCount, "A")) Then

Range(Cells(RowCount, "B"), Cells(RowCount, "D")).Cut _
Destination:=Range("A" & RowCount)


End If

Next RowCount

End Sub


"Blaster Master" wrote:

I've got an excel spreadsheet that is sent to me every 2 - 3 days, with
multiple columns of data in it. For the most part, it will always follow the
same format from left to right. Occassionally, one of the columns of data
sent to me will get mixed up with another one when the sender exports it
from their database. I've spoke to them on numerous occassions about this,
and they insist that the data is that way in their database, so "...it's out
of our control..."

*hangs head*

So now I'm left cleaning up someone else's mess.

There's a good side to this though. I've found a consistent pattern to the
difference between the regular and different formatting.

The spreadsheet has user's first, middle, last name, SSN#, company name,
address1, address2, address3, city, state, zip, phone, fax, email, and some
other fields mixed in there.

The problem comes when the sender's data export gets their client's
"address1" field mixed up with their "company name". When this happens,
"company name, address1, and address2" all shift right one field. Address3
is otherwise NEVER used. So imy question is this.

Is there a way to check for data in a field in a row, then take that field,
and the 2 previous fields, and cut / paste said 3 fields to the left one.
But all other fields of data within that row must be left alone.

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| | Bob's trucks | 45th avenue |#13
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

it should look like

------------------------------------------------------------------------------------------------------------------
| company name | address1 | address2 |address3
| City | State | ZIP |
------------------------------------------------------------------------------------------------------------------
| abc cars | 1 street | suite2 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| Bob's trucks | 45th avenue |#13 |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------
| jerry's pizza | 1201 verizon | 891 S third |
| Little Rock | AR | 71110 |
------------------------------------------------------------------------------------------------------------------

Is there SOME way to automate this? I'm getting 1000+ addresses a week, and
I can't keep doing this.

--


Brad S. Russell