Sorting groups of columns to a related group of columns

Is there a way I can have Excel sort a group of columns with another group and add blank feilds as needed?

Here is what I am doing:
I have one worksheet that has two groups of columns the first group (A-G) has store name, store number, address, city, sate, zip, and phone number.

The second column has store number, and area manager.

I want to be able to sort the columns so the area manager names line up with their associated store number addresses and leave blank cells where there is no area manager for a store, and blank cells where I have no store information.

I have thousands of rows of data to do.

Please see the attached file.

THANKS!

Hi,

Make a copy of the spreadsheet, copy the following code to a blank module (Alt + F11) and run (Alt + F8) the 'Macro1' macro.

Let me know how it goes.

Kind regards,

Robert

Please Login or Register  to view this content.

I am getting a run time error 1004, application-defined or object-defined error. This is what is highlighted when I go into debug:

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers



Thanks,

Jamie

Hi Jamie,

That's weird as I tested the solution before I posted it.

If the store numbers are sorted in ascending order, remove the code after 'Application.ScreenUpdating = False' so that the following code is 'Dim intCutRow As Integer'.

Hope this solves the problem.

Robert

THanks. I removed that code and it ran the script and worked for the example but not completly for my file.

I think the problem was my example did not represent the data close enough.

Please see the new example that will show the misaligned cells once the script is ran. The new cells are highlighted.

Thanks again. I really appreciate your help.

Jamie

Hi Jamie,

This one's beyond me I'm afraid. However, I did discover a free download for a workbook that will do the trick at http://www.myzips.com/download/Excel-List-Compare.phtml

Good luck,

Robert

That macro worked!

Thanks a lot!!!!