+ Reply to Thread
Results 1 to 5 of 5

Expediting Tedious Work

  1. #1
    Registered User
    Join Date
    12-19-2005
    Location
    Cleveland, Ohio
    Posts
    5

    Expediting Tedious Work (Will Pay!)

    I've attached a screen cap below:

    I am working on a large Excel Database file. My next step is to input 2005's Information into the main excel file and arrange the data by zip code. As you can see from the screen cap, the information that I imported into the file is not automatically spaced out to match up Column AV to Column C's Zip Code Arrangements. For Example C6 & AV6 Zip Codes do not match, instead it should be C10 & AV6. Instead of manually moving each Zip Code down to match up zip codes, is there any query or program I can run to space out the zip codes and make them match up zip code for zip code? The data in columns AW & AX also need to shift with the zip codes.

    If someone can help me with this program, I am will to paypal a couple dollars for their help, thanks a lot!
    Attached Images Attached Images
    Last edited by mikemillsjr; 12-19-2005 at 01:55 PM. Reason: Updating

  2. #2
    johnhildreth@citynet.net
    Guest

    Re: Expediting Tedious Work

    The screenshot link didn't work for me. As I understand it, you want
    the data in col AV in the same order as col C. Do both columns have
    exactly the same data? If so, then sorting will work. If not, then
    you will have blank and/or extra rows and a bit of VBA code will
    certainly do the trick.

    More info please.

    John


  3. #3
    Registered User
    Join Date
    12-19-2005
    Location
    Cleveland, Ohio
    Posts
    5
    Check your e-mail John or click here ---> http://www.newtechcity.net/screencaps/screencap1.jpg

  4. #4
    Registered User
    Join Date
    12-19-2005
    Location
    Cleveland, Ohio
    Posts
    5
    http://www.newtechcity.net/files/Extracted%20Data.xls

    I extracted the data out of the main excel file and put them in columns a-d so I didn't have to upload the entire file which is well over 100mbs. You can better understand exactly what I want to do. Columns A & B need to match up zip code for zip code, for example A5 & B5 match, but A10 should match B6. Manually I would have to drag and drop cells for the entire file which will take a very long time. Therefore, Iím wondering if there is a way to make the data from Column B match A. The data for Columns C & D would also have to shift as well. Let me know, thanks!

  5. #5
    Registered User
    Join Date
    11-18-2005
    Posts
    4

    Thumbs up

    See attached zip file for an example of what I describe here.

    Instead of using only one sheet in your workbook use one worksheet for your Master Zipcode list, and put your Imported data on a separate worksheet.
    1. Create a named range for all your imported data.
    (Insert -> Name - > Define)
    type "Data2005"
    Select the bottom image and select the range of your data
    Select Add button

    2. On your master Zipcode sheet in cell B5 enter the formula
    =VLOOKUP($A5,Data2005,2,FALSE)

    3. Repeat for each column you want to display data from i.e in C5
    =VLOOKUP($A5,Data2005,3,FALSE)

    4. Mark formulas and copy to all empty cells in the list

    This doesn't use any Code just built in functions. You could do it with a VBA program also but this is very easy and straight forward.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1