LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default data manipulation quandry

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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
100% quandry Mojo Excel Discussion (Misc queries) 6 June 1st 09 01:56 PM
Multivariable Data Spreadsheet Quandry joebags Excel Discussion (Misc queries) 0 April 11th 06 05:44 PM
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation vmegha Excel Programming 2 December 19th 05 12:14 AM
Macro Quandry Desiree Excel Discussion (Misc queries) 2 July 29th 05 01:32 PM
macro quandry.....?? Daesthai Excel Discussion (Misc queries) 2 June 29th 05 07:29 PM


All times are GMT +1. The time now is 11:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"