Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|