Moving data between columns

Question

Short Question:  How do I move data from one column to another while a filter(s) is on without Excel messing it up?   I regularly find that when I grab a chunk of data, and move it into the correct column (say city to state), the next filter often reveals that that data has to now be moved back because excel grabbed other data as well.  So I'm constantly moving the same data back and forth between the same columns.

In order to not keep repeating this cycle, I find I have to cut and paste each data point one at a time.  For thousands of data points, this is a very tedious and time consuming process.  Any ideas?

Details are below:

**Long Question:**For my job, I have to reformat/reorganize data from a program called Localize Lead Tool that "scrapes" Google for contact information.  However, the data comes into CSV (and thus into Excel) poorly formatted.  Data is often concassonated and data often ends up in the wrong column.  So, for example, data might look a little like this:

100 North Central Expressway, <br>#520, Dallas, TX 45019

The first glance over the information would suggest using Text to Columns (Data | Data Tools).   Indeed, this is useful for MOST of the information.  However, the #520 gets split from its address (offsetting the rest of the contact info by one column) and, TX 45019 has no comma so it stays together.  Also, some data points don't have an address, so City, State and Zip are all thrown off.  Further, some data points have repeat their zip code before the address.

All of this, and probably other factors, require that I reshuffle a lot of data.  Filters combined with ctrl + X would be the most logical answer.  However, when I cut large chunks of data from one column and paste it into the correct data, Excel often goofs up.  When I do a second filter, I discover that I didn't move only the data I wanted to move: I cut and paste ALL the data in between.  Therefore, I'm constantly moving the same data back and forth between the same two or three columns.  Alternatively, I have to cut and paste each data point one at a time.

I have proven this repeated effort by highlighting the blocks of data before I cut and paste them.  Later, the highlighted data shows up in the wrong column and must be moved again.

This is a colossal waste of time.  I've tried it in Excel 2007, 2010 and 2012.  2012 might be better, I'm not sure.

Does anyone have any ideas?

Answer 1

As I have before, I would strongly disagree with you.  The same competent people that occasionally check in to the IT Pro forums seem to monitor this one more consistently.  Pushing people from here to there, given the difficulty of monitoring multiple forums, only serves to decrease the pool of competent responders.

In addition, for this particular problem, the issue is to develop a particular set of rules to go by.  Either the poster can do that himself, or he can post his material and ask for suggestions.  That process is not going to be any different in a different group.

Things were better when you supported newsgroups, as it was not particularly difficult to monitor multiple groups.  But since you dropped that, it has become more difficult for those of us with an interest in helping others with Excel related problems.   Please do not compound that problem with this attitude of sending people elsewhere, with problems that can be responded to in this group.

Answer 2

I really appreciate your answer.  It's great if you know how to use Macros; unfortunately I don't.

Hello,

Thank you for your response.

For issues relating to Macro, your query would be best addressed on the Excel IT Pro forum.

For more assistance with this issue you may contact the Excel IT Pro Forum using the following link.

http://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly. They can either share their knowledge or learn from your interaction with us.

Thank you for your understanding.

Thank you.

Answer 3

Well, once you elucidate rules, it will probably be possible (although more difficult and harder to maintain) to implement them using worksheet formulas, possibly along with "helper columns" (columns set up to hold intermediate results). 

The important thing, if you want to automate the process, is to figure out unambiguous rules that define how you want your data parsed.

While one could figure out a rule that would apply to the single example you have provided, it is unlikely it will apply to your entire data base.

If you want further assistance, you are going to have to supply more data, representative of all the variablility that exists in your real data, as well as examples of desired output.

Answer 4

I really appreciate your answer.  It's great if you know how to use Macros; unfortunately I don't.

Answer 5

Poorly formatted data can be a PITA.  I would suggest parsing the data using a visual basic macro, where you can set up rules that better apply to your data.  This might involve using regular expressions, lookup tables, etc, depending on how variable your data might be.

Certain items might be easy, others more difficult.  But it would be simpler than the method you've adapted, and able to be applicable to more variations (and run faster, too).

If you need help, you'll need to post examples demonstrating all of the potential variability in your data.