Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
I have a worksheet that I need to sort the rows based on two columns.
Now before everyone tries to tell me how to sort using Excel's sort function, I will state that it will not do what I need and this is why and what I need to discover a macro to do... Example: A B ----- ----- 200 300 100 200 100 300 200 100 300 etc, etc, etc After "Sort" Macro: A B ----- ----- 100 100 100 200 200 200 300 300 300 And now for the fun part? The sort on data is NOT in column A & B, but rather in columns M & N and must stay that way. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
not sure if i made the correct assumptions, but i assumed that if there was a
value in M1, N1 would be blank and if there was value in N2, M2 would be blank. i used a helper column (column O) with this formula and dragged it down: =IF(M10,M1,N1) then i selected M,N, and O and sorted on column O. -- Gary "RONZANDER" wrote in message ... I have a worksheet that I need to sort the rows based on two columns. Now before everyone tries to tell me how to sort using Excel's sort function, I will state that it will not do what I need and this is why and what I need to discover a macro to do... Example: A B ----- ----- 200 300 100 200 100 300 200 100 300 etc, etc, etc After "Sort" Macro: A B ----- ----- 100 100 100 200 200 200 300 300 300 And now for the fun part? The sort on data is NOT in column A & B, but rather in columns M & N and must stay that way. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
There are undoubtedly other ways to do this, but what occurs to me (after
some thought, I confess) is to create a helper column -- in Z, let's say -- with this formula: =IF(M2="",N2&"N",M2&"M") That results in col z having the following values:200M, 300M, 100M, 200N, 100N, 300N, 200N etc... Sort on Z and your rows should be in the order you want. Look, ma, no macro! ---"RONZANDER" wrote: I have a worksheet that I need to sort the rows based on two columns. Now before everyone tries to tell me how to sort using Excel's sort function, I will state that it will not do what I need and this is why and what I need to discover a macro to do... Example: A B ----- ----- 200 300 100 200 100 300 200 100 300 etc, etc, etc After "Sort" Macro: A B ----- ----- 100 100 100 200 200 200 300 300 300 And now for the fun part? The sort on data is NOT in column A & B, but rather in columns M & N and must stay that way. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
I guess i should have mentioned that columns A thru V have data, and
when the rows sort all that data must remain intact so I have to sort entire rows, not just columns M & N where the sort values now reside. The first column of data (M) is best described as "parents", and the second column (N) is best described as "children" and I am trying to sort the children to their parents. Thanks for the help so far,but I am trying to make a macro usable by anyone, as I understand installing a helper column, but he people who will actually use this sheet wouldn't have a clue, so a macro is really my only option. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
so you write a macro that adds this formula to the right of all of the data, do
the sort then remove the column with the formula. -- Gary "RONZANDER" wrote in message ... I guess i should have mentioned that columns A thru V have data, and when the rows sort all that data must remain intact so I have to sort entire rows, not just columns M & N where the sort values now reside. The first column of data (M) is best described as "parents", and the second column (N) is best described as "children" and I am trying to sort the children to their parents. Thanks for the help so far,but I am trying to make a macro usable by anyone, as I understand installing a helper column, but he people who will actually use this sheet wouldn't have a clue, so a macro is really my only option. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Unusual Sort Rows Needed
Nah, you didn't need to mention it, I just took it for granted; so, no doubt,
did Gary. After inserting the helper columns, you sort on Z, as I said (or on whatever column you create as your helper), but you sort the entire rows, not just the columns you mentioned. As for the macro, I understand: You want this to be available to every user of this sheet, not just the Excel-savvy. So you put a button at the top of column M, I guess, and any time the user clicks that button it triggers the macro Gary described, which creates the helper column in Z (or wherever you decide), sorts the rows, and then, if you like, deletes the helper values in Z. And that, I guess, is why you put the question in Excel Programming in the first place, because you knew already that a cool sort solution wouldn't be good enough. But here's the next question: Do you know how to write a VBA/Excel program? If you do, then you can work on the one described here, and maybe ask questions if you're not sure how to do some part of it. But if you don't have any idea how to write a VBA program at all, this is maybe not the best place to start. It's not impossible, but it's not a task I would assign as first-day homework to someone who's never written a program before. How much do you already know? And how much effort are you willing to expend to learn? --- "RONZANDER" wrote: I guess i should have mentioned that columns A thru V have data, and when the rows sort all that data must remain intact so I have to sort entire rows, not just columns M & N where the sort values now reside. The first column of data (M) is best described as "parents", and the second column (N) is best described as "children" and I am trying to sort the children to their parents. Thanks for the help so far,but I am trying to make a macro usable by anyone, as I understand installing a helper column, but he people who will actually use this sheet wouldn't have a clue, so a macro is really my only option. --- "Bob Bridges" wrote: There are undoubtedly other ways to do this, but what occurs to me (after some thought, I confess) is to create a helper column -- in Z, let's say -- with this formula: =IF(M2="",N2&"N",M2&"M") That results in col z having the following values:200M, 300M, 100M, 200N, 100N, 300N, 200N etc... Sort on Z and your rows should be in the order you want. Look, ma, no macro! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Sort Formula Help Needed | Excel Discussion (Misc queries) | |||
Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
unusual function needed, unusre how to/if possible | Excel Programming | |||
Help needed with Excel VBA sort | Excel Programming | |||
Sort by specific word, then reg sort. (VB knowlege NEEDED) | Excel Programming |