Need help.

[Paul]

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

 

[Chris Leonard]

Paul.

Try this. You will need to play with the two range values but I've tried
this and it seems to work OK. There may be a neater way of doing it .....
anyone else care to take a stab ?

Sub Sortit()

Set MyFirstRange = Range("A1:A3")
Set MySecondRange = Range("G1:G3")
fnd = 0

For Each c In MyFirstRange
For Each n In MySecondRange
If c.Value = n.Value Then
c.Offset(0, 3).Value = n.Offset(0, 1).Value
c.Offset(0, 4).Value = n.Offset(0, 2).Value
fnd = 1
End If
Next
If fnd = 0 Then c.Offset(0, 3).Value = "No Match"
fnd = 0
Next

End Sub

HTH

Chris

 

[Guest]

Do you need a macro or quick solution?

Try adding two columns to your spreadsheet.
One for the master customer list and the second for the compressed data.
First column number the master customer list.
Second column use a vlookup formula to pull the master customer number
Then do a data sort on the compressed list


----- Paul wrote: ----

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

Exampl

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 appreciate
Thanks in advanc
Pau