Move Coloured Cells

Hi,

I am quite new to using excel for complex problems.

I work for a real estate company and I have a large list of names and their appropriate properties to sort through. This list includes business names as well as residential.

All the names (residential and business) were in one cell. eg. [John Thomas Smith] or [ABC Building PTY LTD]. I need the first and last names in separate columns so that I may transfer the info in bulk to another sheet. I used an add-on for excel that split all the names into columns, eg. [John] [Thomas] [Smith] or [ABC] [Building PTY] [LTD].

In order to sort the business names out as I don't need them right now, I have used the ctrl + F feature to find the 'PTY' or the 'LTD' suffix and highlight all cells containing them red.

Because the business names don't need to be split up like the residential names how can I take any row with red in it and move all the info in the cells into the first cell.

So that wherever one of the Red cells appears due to containing a business suffix, that cell and all the others on that row, which contain the full business name, move to the very left cell of the row?

I know it is hard to understand, but any help is appreciated

Cheers,
--CameronP

I will try to make something similar,

The worksheet I am using however was not put together well by the source and so the layout and way that it has been made effects the problem.

I unfortunately cannot upload the worksheet seeing as it contains people's private information such as properties that they own as well as postal adresses.

But to try and make the issue simpler, imagine you had a spread sheet that is 3 columns wide with names split across the columns organised in order of First Name, Middle Name and Last name. There is approx. 5500 rows of data.

Random rows of the data contain 1 cell that is red due to the way that I did the sorting, I want those rows with a red cell to take all the information from the 3 cells and combine it into 1.

Hope you understand the issue.

Cheers.

Hi, Cameron

Without seeing your actual data, I have a suggestion on which you may build a solution.

I am assuming that when you state that your names are in one cell, that you mean one column.

If you were to copy that column to a new worksheet.

Then select the column by clicking on the column Letter. say "A".

Then in the headings above select DATA, then select "Text to Columns"
Step 1 Select Delimted then Next
Step 2 Untick Tab and Tick Space
Step 3 Click on Finish.

You may then rejoin certain columns using "&" ie A1&" "&A3

You can do sort's to try and line up your data.

Try it.

Cheers

TonyB

Here are two spreadsheets.

'Help1' is a mock-up, very small simulation of the source document I am working with. The goal is to get a spreadsheet where each individual property owner/business is listed and then the properties that they own are associated with them. Currently the document lists a large number of properties individually, repeating owners if they own more than one property.

This data is going into our Databasing software, however you could imagine that having one person in the database multiple times will wreak havoc on the system, especially since some people own 20+ properties.

'Help2' is a mock-up of what I have done with the names. Because of the software that we use for databasing, I have to input spreadsheets in a certain format, that format requires that last names and first names are in different columns all together. I used an add-on for excel to split up all the names from the original into a format similar to in 'Help2'. I have highlighted all the business listings in red and I need a way to move all the data on the red Rows left so that it is all contained in the first cell of that row.

If you can help me resolve this that would be greatly appreciated.

Cheers
CameronP