Expediting Tedious Work (Will Pay!)

[mikemillsjr]

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!

 

[mikemillsjr]

The rest of the date should shift with column B, for example if B6 needs to shift down to B10 to match A10, then B6, C6 and D6 needs to all shift down to A10 to match up with Column A. There isn't a return for every zip code that's why I have to figure out how to shift this data down, if I had a return for every zip, everything would match up.

 

[mikemillsjr]

Correct me if I'm wrong, but you want

01001 1001 65 2
01002
01003
01004
01005
01007 1028 73 1
...

It should read:

01001 01001 65 2
01002
01003
01004
01005
01006
01007 01007 37 1
01008
01009
01010
01011
01012
01013 01013 38 1
...

 

[mikemillsjr]

I apologize I may have thrown you off by not updating Column B's Zip Code format. I guess when you import zip codes, excel treats them like numbers and removes the first zero if applicable, never-the-less, I uploaded an updated copy of the file, same link...

http://www.newtechcity.net/files/Extracted Data.xls

 

[Norie]

mike

Did you try my VLOOKUP suggestion?

 

[mikemillsjr]

I attempted to but I don't know if I did it correctly or not, if you click on the link above the file is there, could you run it and tell me what you get?

 

[Norie]

mike

I have downloaded your file.

I inserted 2 new columns and entered this formula in B5.

=VLOOKUP(A5,$E$5:$G$9536,2,0)

I then double clicked the bottom right of the cell to copy it down.

I entered this formula in C5 and copied down as above.

=VLOOKUP(A5,$E$5:$G$9536,3,0)

The inserted formulas returned a lot of #N/A errors for the zip codes that are in column A but not in column E.

These can easily be eliminated.

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Zipper()
<SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Sheets("Main")
    <SPAN style="color:#00007F">Set</SPAN> Rng = .Range("C5:C" & .Cells(Rows.Count, "C").End(xlUp).Row)
    .Columns("AV:AX").Copy Columns("BI")
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
        i = 0
        i = Application.WorksheetFunction.Match(c.Value, .[BI:BI], 0)
        <SPAN style="color:#00007F">If</SPAN> i = 0 <SPAN style="color:#00007F">Then</SPAN>
            .Range("AV" & c.Row & ":AX" & c.Row).ClearContents
        <SPAN style="color:#00007F">Else</SPAN>
            .Range("AV" & c.Row & ":AX" & c.Row).Value = .Range("BI" & i & ":BK" & i).Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> c
    .[BI:BK].Clear
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Assumes BI:BK is available for temporary use.

EDIT -- Always test on a backup.

 

[mikemillsjr]

Jon, where would I post that script to execute it?

 

[just_jon]

Put it in the sheet module or ThisWorkbook or a standard module, shouldn't matter.

Hit Alt+F11 to open the VB Editor, then locate your filename in the projects pane at the left.

Click either the sheet name holding the data, or the Thisworkbook module.

Copy/paste the code there.

To run, go back to XL and click Tools | Macro | Macros

Select Zipper, click Run

 

[mikemillsjr]

I get a subscript out of range error?