Expediting Tedious Work (Will Pay!)

mikemillsjr

New Member
Joined
Dec 19, 2005
Messages
14
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

New Member
Joined
Dec 19, 2005
Messages
14
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mikemillsjr

New Member
Joined
Dec 19, 2005
Messages
14
Sharkie21 said:
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
...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
mike

Did you try my VLOOKUP suggestion?
 

mikemillsjr

New Member
Joined
Dec 19, 2005
Messages
14

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

<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.

:eek: EDIT -- Always test on a backup.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,359
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top