I have created a worksheet that sorts data I copy from a
different file. The data after it is sorted is comprised
down to 3 columns of information with the first column
(AA) being the customer's name, followed by sales, ytd,
and last year total. I have a master list of all customers
in column b. What I want to do is sort the compressed data
based upon where the master record in located in the list
of rows.

Example

b10 = Smith Barney - aa3 = Smith Barney. I want to move
cell aa3-ad3 to cell aa10-ad10 to match up the records.
This could be done manually, but I have 6000 records to
adjust. Keep in mind that all information below aa3 needs
to move along with aa3-ad3 to keep all data in tack. To
add a little more complexity to this, the names are sorted
by Canadian and US customers. With empty cells separating
the two sets of information chains.

Can someone help me with a loop statement that will make
this easier than what I am doing now? (Manual)

Any help will be truly appreciated
Thanks in advance
Paul

Are the names ALWAYS listed exactly the same way? Always last nam
first and always all caps and always spelled the same way? - Piku

--
Message posted from http://www.ExcelForum.com

Try this:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim x As Long
Dim y As Long
Dim a As Long
Dim ws As Worksheet

Set ws = Worksheets(1)
lrow = ActiveSheet.UsedRange.Row - 1
ActiveSheet.UsedRange.Rows.Count

For x = 2 To lrow
cust = ws.Cells(x, 27).Value
For y = 2 To lrow
If ws.Cells(y, 1).Value = cust Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(y, 31).Select
ActiveSheet.Paste
End If
Next y
Next x
ws.Range(ws.Cells(1, 27), ws.Cells(1, 30)).Cut
ws.Cells(1, 31).Select
ActiveSheet.Paste
a = 1
For x = 2 To lrow
If ws.Cells(x, 27).Value < "" Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(lrow + a, 31).Select
ActiveSheet.Paste
a = a + 1
End If
Next x
For x = 1 To 4
ws.Columns(27).Delete
Next x
Application.Goto ws.Cells(1, 1)
Application.ScreenUpdating = True
End Sub

- Piku

--
Message posted from http://www.ExcelForum.com

Pikus

This is Interesting that you put this code in a
CommandButton Format. I don't think I ever have used
something like this before? Can you help me, because when
I put this code into a CommandButton from the Control
ToolBox menu I can not figure out how to activate the
macro to begin to work?

Also if I just put the code into a normal sub file I get
all sorts of errors.

Any thoughts
Paul


-----Original Message-----
Try this:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim x As Long
Dim y As Long
Dim a As Long
Dim ws As Worksheet

Set ws = Worksheets(1)
lrow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count

For x = 2 To lrow
cust = ws.Cells(x, 27).Value
For y = 2 To lrow
If ws.Cells(y, 1).Value = cust Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(y, 31).Select
ActiveSheet.Paste
End If
Next y
Next x
ws.Range(ws.Cells(1, 27), ws.Cells(1, 30)).Cut
ws.Cells(1, 31).Select
ActiveSheet.Paste
a = 1
For x = 2 To lrow
If ws.Cells(x, 27).Value < "" Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(lrow + a, 31).Select
ActiveSheet.Paste
a = a + 1
End If
Next x
For x = 1 To 4
ws.Columns(27).Delete
Next x
Application.Goto ws.Cells(1, 1)
Application.ScreenUpdating = True
End Sub

- Pikus


---
Message posted from http://www.ExcelForum.com/

.

Pikus

The compressed data in coloums aa-ad will be an ever
changing list of customers depending on who has boughten
from use in the past month. But the list in coloum b will
always remain the same unless we get a new customer that
has never boughten from us before. I just plan on adding
that customer name as it happens. The names are always
spelled the same and listed in the same manner (Alfa A-Z).

Paul

-----Original Message-----
Are the names ALWAYS listed exactly the same way? Always
last name
first and always all caps and always spelled the same
way? - Pikus


---
Message posted from http://www.ExcelForum.com/

.

I don't believ any of the changes you mention should affect th
functioning of the ode. If you do want to put it into a button, inser
a button, enter design mode if you are not already there an
right-click the button and select "View Code" from the pop-up menu.
That will take you right there. - Piku

--
Message posted from http://www.ExcelForum.com