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
|
Similar Threads
