moving and filtering data :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

moving and filtering data
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

SpringP
Welcome to the Board


Joined: 19 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

moving and filtering data

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

Post Fri Sep 19, 2003 3:56 pm 
 View user's profile Send private message

Mark W.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: moving and filtering data

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

Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
1
ABC CompayABC Compay123 Main St, Austin, Texas, 78704512-555-1212  
2
123 Main St, Austin, Texas, 78704     
3
512-555-1212     
4
XYZ Inc.XYZ Inc.456 Main St, Austin, Texas, 78704512-555-1213  
5
456 Main St, Austin, Texas, 78704     
6
512-555-1213     
7
Acme, Inc.Acme, Inc.888 Blueberry, Austin, Texas, 78704512-555-1214  
8
888 Blueberry, Austin, Texas, 78704     
9
512-555-1214     
10
      
11
      
Sheet2 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


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.

Post Fri Sep 19, 2003 4:04 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: moving and filtering data

Try this:

code:

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:

Microsoft Excel - CreateDatabase(6).xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
1Company Name    
2
1Street Address, City, State, Zipcode    
3
1Phone Number   
4
2Company Name    
5
2Street Address, City, State, Zipcode    
6
2Phone Number   
7
3Company Name    
8
3Street Address, City, State, Zipcode    
9
3Phone Number   
10
4Company Name    
11
4Street Address, City, State, Zipcode    
12
4Phone Number   
Sheet4 

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Post Fri Sep 19, 2003 4:21 pm 
 View user's profile Send private message

SpringP
Welcome to the Board


Joined: 19 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

Re: moving and filtering data

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


Last edited by SpringP on Fri Sep 19, 2003 4:43 pm; edited 1 time in total

Post Fri Sep 19, 2003 4:25 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: moving and filtering data

Try the macro I posted.

Post Fri Sep 19, 2003 4:28 pm 
 View user's profile Send private message

Mark W.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: moving and filtering data

quote:
Originally posted by 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.


Make sure you specify all of your requirements.

Post Fri Sep 19, 2003 4:31 pm 
 View user's profile Send private message

SpringP
Welcome to the Board


Joined: 19 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

Re: moving and filtering data

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

Post Sat Sep 20, 2003 5:51 am 
 View user's profile Send private message

SpringP
Welcome to the Board


Joined: 19 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

Re: moving and filtering data

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 [u]0[/u]7856. 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

Post Sun Sep 21, 2003 5:46 am 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: moving and filtering data

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

code:

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.

Post Sun Sep 21, 2003 8:15 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.