still trouble with sorting

How is you doing your sort? If you're doing it in code with an algorithm like QuickSort then can't you simply modify your code to notionally add a '0' to the end of any 3-character string (ie. '1-1' becomes '1-10')?

Backup your data.
I have not tested this code on a large worksheet.
I would assume that on a large worksheet, it runs very slow.
Run: Run_DoubleNumberSort
Change Column number as needed:

Please Login or Register  to view this content.

True = Exclude Row 1; False Include Row 1

Please Login or Register  to view this content.

Thanks everyone for trying to help me. It's appreciated.

In column B I have data that NEEDS to stay in the format it is in. I have a couple of macros but I don't know if they upload with the workbook or not. Basically, the macro's take the data from column B and pastes it to column K. (from all sheets into a single column on the first sheet)
From there I "try" to sort it but the sort doesn't work right sometimes.

What I really want to do is group the data that is continuous in column k into whatever the data is in that column. For instance, 1 column for all numbers starting with "1-" a different column for all numbers that begin with "2-" etc....
The B column will change once in a while depending on the situation so at some point their might be a number in that column and there might not.

The data starts at 1 and goes to 48 before I start a new number
for example.
1-1
1-2
1-3
....up to 1-48
and then it goes:
2-1
2-2
2-3....up to 2-48

and so on and so on.
The numbers in column B are put in manually.
I don't need anything like manually putting numbers into column B and have it automatically populate a different column, I can do that manually with a the " Sub MakeSummaryTable" macro. Unless there is a way that when I put in a number in column B of say 1-21 then it automatically goes into a column of 1's (does that make sense?)

If my macros didn't stick with the sheets then I'll post them here.

http://dl.dropbox.com/u/29090271/test.xls


Macro to get data from column B into column K:

Please Login or Register  to view this content.

Data to sort column K:

Please Login or Register  to view this content.

Note: There will be other data in the other columns between column B and column K but I'm only concerned with column B.

I made a couple minor changes, and it was able to sort all of your worksheets without problems.
I no longer ask if you have a header, rather I now look for the first row with a "Double Number"
I no longer ask for the column number, but use column letter instead.
If you switch columns, then you can change the line:

Please Login or Register  to view this content.

To any column A-Z (excluding columns AA and higher).

Right now it only sorts one worksheet at a time, if you would like me to re-write it to sort a number of worksheets (one after the other), I can.

Please Login or Register  to view this content.

That is so close to what I am looking for but there are a few minor bumps.
The data numbers in column A and column B need to stay in whatever order they are in. Column B corresponds to a column to the left (column A)
That's why I was pasting column B over to column K so I could just organize the numbers without interfering with the originals.
Basically, I just want to see what numbers from column B are being used and it's easier to have an order then I know what I have left to use.
I work for a phone company and in column A are the cable pairs for dialtone per user and column B is the blade and channel that gives the
pairs the dialtone.

For instance, if you wanted dialtone at your house we would need to assign a cable pair (column A) and a blade and port (column B)
so as an example we could assign you pair 100. We would then assign a blade number (say blade 2) and a channel from that blade (say 6). (There are 48 ports per blade hence the 1-1 to 1-48 and 2-1 to 2-48 etc....)
So the whole thing in column A and column B would look something like this:

Column A --- -- Column B
Pair ---------- Blade Port
100 ---------- 2-6

A blade and port are always in this layout:

Blade Port
1 - 1
1 - 2
1 - 3
etc... up to 1-48
then it goes to:
2 - 1
2 - 2
2 - 3
etc,,, up to 2-48

and so on.



The blade and ports in column B are not always in order. It just depends on what we have available to use with a pair. The pairs however (column A) always stay in order.
All I'm trying to do or should I say, you is get a quick view of the available blades and ports in an order that I can quickly look at and determine what I still have available. That's why I was doing this all on the main sheet. However, if ther is a way to automatically create a new sheet and paste the information there, that would be great also.

and yes, if it could be done to include all blade\ports (column B) from all worksheets that would be extra great.

On a side note. I just want to thank you for giving me a hand on this. Or should I say, doing this for me. You do realize that if I come back to work with this I'm going to be a hero

All the blades and ports ( column B) need to be copied. I don't need column A copied at all. Blades and ports will always be in column B.
New sheet name can be "blades".
All blades and ports from all worksheets need to be copied.
all blades and ports starting with 1- in one column......2- in another column and so on......
Pair numbers never change. they are physical ( physically outside). Blades and ports however can change, the are electronic equipment) Hence the reason for only needing the blades and ports. I just need to see what is left for me to use....
For instance if blade and port 1-1 to 1-25 are used and also blade 1-27 to 1-47 are used then if I had an order I could see that I still have blade 1-26 and 1-48 to use. That make sense?

so all in all I need column B from every worksheet copied to a new sheet called blades ( can the sheet be made automatic?) Those blades and ports could be in an order of:
1 column with all blades starting with the number 1-
1 column with all the blades starting with the number 2-
1 column with all the blades starting with the number 3-
and so on....
now that I have you committed to this project , what are the chances that the new sheet "blades" could be updated automatically when I add or remove a blade and port?




Thanks you

That looks really good Steven. Thanks alot.
FYI, 13-48 is the last blade and port.

can the blanks be removed also? ( on the "blades" sheet) I had that option on my original sheet but it only worked on column k
There are some cells that contain words..I don't need them. Don't know if you have to write something to omit them or not.

I have to keep saying you are a genius because I am totally taking advantage of your skills

Perfect order on the blades sheet Steven. One slight problem. On the blades sheet, it lists all blades and ports( 1-1 to 1-48 thru 13-1 to 13-48) Not just the ones that are actually in column B of each sheet.
I hope you make money with your skills.
Little note:
If I just fill one cell into column B ( 1-1) , it makes a blade sheet but instead of having 1 cell populated ( 1-1), it populates the entire row with the blade and the first port (1-1...2-1---3-1---4-1 etc....)

Steven...you are an Excel God.
2 small things and I swear your're done.

What I'm thinking is, after looking at it, it is a little hard to determine what blade\port combination is available. If it's possible,maybe a blank cell after the order stops? If balde 1-1 and 1-3 are used then the area between 1-1 and 1-3 (1-2) would be left blank.....or some kind of indication on what is available to use..
makes sense?

also, If I have a colored blade\ports number like 1-1, could that transfer over also? In other words, can you add color to the blades sheet if you have some colored blade\ports? I forgot, we use red to indicate that that blade\port combination is on hold for people that move away for the winter). I could just color the cell and not the numbers if that would be easier..


You know the portion function I need to put into the blades sheet to automate the sheet,

('Private Sub Worksheet_Activate()
' SortPortsByBlades
'End Sub)

is there a way to just have the macro add that portion for me? Otherwise, I will need to modify every workbook sheet twice. And I have a lot of workbooks.

If you can't or don't want to then don't worry about it. What you've done is amazing to me.
Thank you. I'm gonna do some testing but I think you nailed it.

and if you have a paypal account I would be more than happy to send you a contribution for your troubles.

Thank you so much.

I was thinking about suggesting something like that also. For example, you could have a new sheet of unused ports. Or you could have all the ports listed and color code unused cells. Or, as you suggested, add blanks.

I like this idea...
I'll post a workbook that I use...It has more worksheets than most of them.
I had to remove private information such as name,address and phone number....

Don't worry if you can't do anything with the red text..that is the lest of any problems....
The suggestion about the used ports would be great.

Hey Steven, I'm getting a debug error from:
.Range("A1").Select

It runs the first time no problem but usually on the second or third try it errors. Is that because of the three line code to the module of the "Blades" worksheet?
Tried it on multiple worksheets. same thing after a bit.....any thoughts?

if you are talking about the blades sheet as far as updating the sheet when I open it, couldn't I just skip the 3 lines of code and just re-run the macro?

I probably confused you by telling you could just delete those lines of code. Once "Blades" is created, the macro will skip over those lines by itself. There is no need to delete them.

If there is no "Blades" sheet. The macro "CollectBladesAndPorts" will create a sheet called "Blades" and attempt to write the three lines of code to the module of the worksheet "Blades." If it fails to do that, then the module of the worksheets Blades will need to have the code added to it manually. You will find a copy of that code at the bottom of the code which I gave you. You will need to copy those three lines and follow the instructions. Once that is done, it is no big deal.

Or, you could delete "Blades" and try running it again. :-)

I'm getting a debug error from:
.Range("A1").Select

It runs the first time no problem but usually on the second or third try it errors. Is that because of the three line code to the module of the "Blades" worksheet?

Add the line:

Please Login or Register  to view this content.

Before:

Please Login or Register  to view this content.

That will take care of the problem.

The bad news is that the part of the code which writes the three line code to the module of the "Blades" worksheet doesn't seem to work all the time. It seems to work best if the VB editor is open when the code is run for the first time.

Is their any harm in having this if it doesn't work correctly? Is there potential to make that code crash the program? If not then It's good by me.
If I have to manually add the code then I can live with that.

I'm going to be testing this code tomorrow and I'll let you know how things go.
Thanks Steven. You did a great job and I appreciate it.

Is their any harm in having this if it doesn't work correctly?

No. I added the proper error checking code so that if it doesn't work, it will simply skip over it.

Personally, I would delete it. Call me ****-retentive or whatever, computer code is supposed to work right every time. But I'll leave it up to you. :-)


P.S. The forum automatically censored my remark!

Steven. Every once in awhile I'll get a compile error: sub function not defined and it highlights this: Private Sub Worksheet_Activate() on the sheet "blades"
I get that when I click on the Blades sheet.

I've also encountered a 400 error. It just says 400 and an ok button...
Still doing some testing...

Update: took the code out of the sheet 'blades" and the first error seems to be gone. I can live with having to rerun the macro to update the blades sheet but still getting error 400 sometimes. It's random as far as I can tell.
Update2:
I've been trying it on a few different workbooks and no errors yet. Wondering if there is something on my sheets that might be causing that\those errors?

It looks real good Steven..

That's what it says in the blades sheet.
Not sure what you mean by "are you sure you changed this after my last code?"
I just copied your latest code to a worksheet module. I didn't change anything. It's been off and on with putting the code for the blades sheet so a few times I had to add it manually but I copied and pasted. ( not a big deal)
I've tried it on 3 different worksheets and so far ne errors. Maybe I mis copied or something...
I'm at home right now testing. When I go to work I'll do some more testing to see if any errors come up.

Update:
Just added the code to a different worksheet and got the error: compile error: sub function not defined:

Private Sub Worksheet_Activate()
CollectBladesAndPorts

End Sub

Then when I close it down and restart it, I get a 400 error.
Little more info.
I just deleted the code and removed the blade sheet. closed the program and restarted it. Re-inserted the code and ran it.
It didn't create the code:
Private Sub Worksheet_Activate()
CollectBladesAndPorts

and gave me a 400 error.
trying to figure out the common denominator of the problem...
I removed the sections you were concerned with, deleted the blades sheet and re-ran the macro on the same sheet that was giving me the error. No errors yet.
If that is the cause of the errors then I can live without it.

Update..nope. Still get the 400 error

Do you have two workbooks open when this error occurs?

P. S. I don't know why I asked this question without trying it for myself. I opened two workbooks with the same code and it didn't create your problem.

So you're saying that just by going back and forth between your other worksheets and blades (while changing some blade-ports to red), you get: compile error: sub function not defined?

Often "compile error: sub function not defined" refers to a spelling mistake. For example:

Please Login or Register  to view this content.

if I remove a letter from "CollectBladesAndPorts" I'll get the error: sub function not defined

But if it is working sometimes and not others, it can't be a spelling mistake.

I got the error too!!

I also discovered the problem!!!

Eh ... there was no code in the standard module! :-)

The worksheet you sent me didn't have any standard modules! Let along my code in it. (see P.S. at the bottom of this message)

So I wonder, perhaps this might work.

Create a worksheet called "Blades" if it doesn't exit.

Copy the following code in the worksheet module:

Please Login or Register  to view this content.

What do you think?

P.S. Looking at things again, I did find my code. You placed it in a "Worksheet" module. And I suspect that (in part) was the problem. You can use the above method.

Or you can place the previous code in a standard module.
In the VB editor, from the menu, you need to do Insert -> Module.
The new standard module will have a name "Module1"
You can remain "Module1" by going to the properties window, and next to:
(Name) Module1
change it to:
(Name) M_Blades
Or, you can leave it as is.
Now with Module1 (or M_Blades) selected, you can paste in the previous code.

The differences as I see it are these:

Re: Blades_In_Blades
Positive: To copy and paste this code into another workbook, you need to create the worksheet "Blades" and copy the code to the module of the worksheet "Blades" and you are done. Everything is in the worksheet module and you don't need to copy a three-line code.
Negative: If someone deletes the "Blades" sheet, the code disappears.

Re: Blades_In_Module
Negative: To copy and paste this code into another workbook, you have to copy the code into a standard & then copy the three line code to the module of the worksheet "Blades" (thus there are two codes to copy).
Positive: If someone deletes the worksheet "Blades", the code still exists. Run: CollectBladesAndPorts and it will create a new worksheet "Blades" (but the three-line code will still need to be copied into the worksheet module).

I guess I could have uploaded a third copy. It would be like "Blades_In_Module", except that when the worksheet "Blades" does not exist, it would create a new worksheet "Blades" and attempt to write the three line code into the worksheet module "Blades". I'll upload it if you want. The downside of this version of the code is that the part of the code which attempts to write the three-line code to the "Blades" worksheet module doesn't always work.

P.S. It is up to you, but I was thinking that the Blades_In_Blades method might be easiest for you.