Macro to move cells to one row

[newyorknix]

Hi,
I have addresses like this:
Each in one cell as a row
Name, phone number, address and zip code

XYZ Ltd 2233223 345, Church Road, NY 8999
XYZ Ltd 2233224 345, Church Road, NY 8999
XYZ Ltd 2233225 345, Church Road, NY 8999
XYZ Ltd 2233226 345, Church Road, NY 8999
ABC Ltd 2345644 34, Bridge line, NJ 8998
ABC Ltd 2345645 34, Bridge line, NJ 8998
ZXY Ltd 5423455 34, Bridge line, NJ 8998

I want a macro to move phone number to adjacent cells in the same row, if say name or address matches... And it may either empty the "phone number" cell or remove the row...

This should be only up to three phone numbers. It may either empty the "phone number" cell or remove the following rows...

Rows with no match or single phone number rows should be unaffected.

Like this:

XYZ Ltd 2233223 2233224 2233225 345, Church Road, NY 8999
ABC Ltd 2345644 2345645 34, Bridge line, NJ 8998
ZXY Ltd 5423455 34, Bridge line, NJ 8998

Thanks in advance
Regards,
Nicks

 

[obiron]

Will the addresses always be seqential in the worksheet (as shown) or could they be spread about (e.g.)

XYZ Ltd 2233223 345, Church Road, NY 8999
ABC Ltd 2345644 34, Bridge line, NJ 8998
XYZ Ltd 2233226 345, Church Road, NY 8999
ABC Ltd 2345645 34, Bridge line, NJ 8998
XYZ Ltd 2233225 345, Church Road, NY 8999
ZXY Ltd 5423455 34, Bridge line, NJ 8998
XYZ Ltd 2233224 345, Church Road, NY 8999


What happens if there is more than three records for an address

What happens to the lines that have been de-duplicated
What happens if the same phone number appears on the same address line (e.g.)

XYZ Ltd 2233224 345, Church Road, NY 8999
XYZ Ltd 2233224 345, Church Road, NY 8999

are phone numbers to be de-duplicated on company name only, or name and address

Is the data in one cell or in multiple cells
If multiple cells, do you need the adresses to always be aligned (i.e. potentially empty cells where phone numbers don't exist)

 

[newyorknix]

Hi,
Thanks for the fast reply...

Will the addresses always be seqential in the worksheet (as shown) or could they be spread about (e.g.)

Sorry... I am not getting this question, I sorted the rows with name as of now... it can be random in the output...

What happens if there is more than three records for an address

You can delete all the rows other than first three matches...
or empty all the "phone numbers" cells after three matches (so that I will remove them by F5, choose blank and delete sheet rows)

What happens to the lines that have been de-duplicated
What happens if the same phone number appears on the same address line (e.g.)

XYZ Ltd 2233224 345, Church Road, NY 8999
XYZ Ltd 2233224 345, Church Road, NY 8999

are phone numbers to be de-duplicated on company name only, or name and address

There are no duplicates in phone numbers but name and address have... Like this the same company has multiple address too.

XYZ Ltd 2233224 345, Church Road, NY 8999
XYZ Ltd 2233545 345, Church Road, NY 8999
XYZ Ltd 2233893 45, Ring Road, NJ 8998
XYZ Ltd 2233234 45, Ring Road, NJ 8998

So it would be better to sort by address

Is the data in one cell or in multiple cells
If multiple cells, do you need the adresses to always be aligned (i.e. potentially empty cells where phone numbers don't exist)

Data is in multiple cells and by rows like the example below (actually I used double space but it did not show up in the first post)... and special thanks for asking this...

XYZ Ltd | 2233223 | 345, Church Road, NY | 8999

I separated each cell by "|"

Yes, address should be aligned always and it is fine to have empty cells with no numbers...
XYZ Ltd | 2233223 | 2233224 | 2233225 | 345, Church Road, NY | 8999
ABC Ltd | 2345644 | 2345645 | | 34, Bridge line, NJ 8998
ZXY Ltd | 5423455 | | | 34, Bridge line, NJ 8998

Thanks & Regards,
Nicks

 

[obiron]

Quote:
<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Will the addresses always be seqential in the worksheet (as shown) or could they be spread about (e.g.) </TD></TR></TBODY></TABLE>
Sorry... I am not getting this question, I sorted the rows with name as of now... it can be random in the output...

If we know the data is already sorted (or at least grouped) by company name and address we can run down the file comparing this row with the previous. If not then we have to use the countif function which will be slow.

If there are more than three phone numbers, how will we know which 3 to take (1st three in the file, lowest three, highest three)

OK de-dupe by name and address - much easier if the data is already sorted

can you provide a sample file of say - 50 addresses: 100 rows to test with.

 

[newyorknix]

Hi,

If we know the data is already sorted (or at least grouped) by company name and address we can run down the file comparing this row with the previous. If not then we have to use the countif function which will be slow.

The data is sorted only with company name like this and so on

ABC Ltd 2345644 34, Bridge line, NJ 8998
ABC Ltd 2345645 34, Bridge line, NJ 8998
XYZ Ltd 2233223 345, Church Road, NY 8999
XYZ Ltd 2233224 345, Church Road, NY 8999
XYZ Ltd 2233146 24, Ring Road, NY 8999
XYZ Ltd 58733226 24, Ring Road, NY 8999
ZXY Ltd 5423455 34, Bridge line, NJ 8998

If there are more than three phone numbers, how will we know which 3 to take (1st three in the file, lowest three, highest three)

First three phone numbers is good enough to go...

 

[newyorknix]

Sample file attached here...

https://docs.google.com/spreadsheet/ccc?key=0ArKErbNhyvu7dGJEUm92UkppQU5vaGJ4NXlhQXhTMHc&hl=en_US