moving and filtering data

[SpringP]

i have a series of rows in a column that has customers (200+ cust)info in it.

Company Name
Street Address, City, State, Zipcode
Phone Number

i need it to be

|Company Name | Street address | City | State | ZipCode | Phone Number |

| = column

thank you for any advice or help

 

[Mark W.]

I do it as a 2-step process... copying the yellow highlighted area and pasting into the pink highlighted area...

Book1
	A	B	C	D	E	F
1	ABCCompay	ABCCompay	123MainSt,Austin,Texas,78704	512-555-1212
2	123MainSt,Austin,Texas,78704
3	512-555-1212
4	XYZInc.	XYZInc.	456MainSt,Austin,Texas,78704	512-555-1213
5	456MainSt,Austin,Texas,78704
6	512-555-1213
7	Acme,Inc.	Acme,Inc.	888Blueberry,Austin,Texas,78704	512-555-1214
8	888Blueberry,Austin,Texas,78704
9	512-555-1214
10
11
Sheet2

Then after inserting 3 additional columns to the right of column C:C I'd use the Data | Text to Columns... menu command to parse the comma delimited address info.

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim x As Long
    Dim r As Long
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    Rng.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
    Set ShNew = Worksheets.Add
    r = 1
    For x = 1 To Rng.Rows.Count Step 3
        ShNew.Cells(r, 1) = Rng.Cells(x, 1)
        ShNew.Cells(r, 2) = Rng.Cells(x + 1, 1)
        ShNew.Cells(r, 3) = Rng.Cells(x + 1, 2)
        ShNew.Cells(r, 4) = Rng.Cells(x + 1, 3)
        ShNew.Cells(r, 5) = Rng.Cells(x + 1, 4)
        ShNew.Cells(r, 6) = Rng.Cells(x + 2, 1)
        r = r + 1
    Next x
End Sub

It worked with this data in Sheet1:

CreateDatabase(6).xls
	A	B	C	D
1	1CompanyName
2	1StreetAddress,City,State,Zipcode
3	1PhoneNumber
4	2CompanyName
5	2StreetAddress,City,State,Zipcode
6	2PhoneNumber
7	3CompanyName
8	3StreetAddress,City,State,Zipcode
9	3PhoneNumber
10	4CompanyName
11	4StreetAddress,City,State,Zipcode
12	4PhoneNumber
Sheet4

 

[SpringP]

i need to code a macro to do the process quicker, also how would i change the format of the address from |Street address, city, state, zipcode| into |street address|city|state|zipcode| how do i accoplish this.

EDIT: the code you gave did work but on occasions there could be two or more telephone numbers and the format is sckewed

 

[Andrew Poulsom]

Try the macro I posted.

 

[Mark W.]

i need to code a macro to do the process quicker, also how would i change the format of the address from |Street address, city, state, zipcode| into |street address|city|state|zipcode| how do i accoplish this.

Make sure you specify all of your requirements.

 

[SpringP]

Andrew Poulsom, the macro you posted did work. my database has a few missing parts on ocassion. for instance one some doesnt have address but phone number, some has multiple phone numbers. but after a few tweaking the code eventually work.
alot of thanks

 

[SpringP]

ohh yeah theres also one more thing. after the data has been moved the zip codes are changes. since im in the north east we have a lot of zero on the front, for example 07856. when that value is moved to another column it is omited, the data becomes 7856 instead. i know i could format the cell to automatically know that its a zip code and it adds the zero. but when i save the file and open it with another program its not there. i tried changing the column to format as a "text" but then i have to input it manually to hundreds of rows. is there another way to do this?
thanks

 

[Andrew Poulsom]

First change the TextToColumns line so that column 4 is Text:

Rng.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ 
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ 
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2))

Then change this line:

ShNew.Cells(r, 5) = Rng.Cells(x + 1, 4)

to:

Rng.Cells(x + 1, 4).Copy ShNew.Cells(r, 5)

so that it remains as Text when transferred to the new sheet.