Question For Excel Gurus

Jitter · Aug 10, 2010, 08:48 PM

I am trying to make what I guess would be called a rotating list. Basically I want to set something up like this:

Name Code#
Tom 3
Bill 1
Jim 1
Bob 3
etc......

If you input a code of 1 for a person I want their name/code to goto bottom of list and all other rows to move up. If you input a 3 for a person I want that person/row to goto top of list and all other rows to move down.

Is this possible in excel?

Thanks
Kevin

Takaharu · Aug 10, 2010, 11:10 PM

How about sorting by column after changing a number from 1 to 3 (or vice-versa)?

Say column A has the names and column B has the numbers, highlight column B, click sort then Excel should ask if you want to include column A - say yes and you should have column B sorted numerically.

Jitter · Aug 11, 2010, 01:18 AM

well that would kinda work, but this is a mandation list for a nursing home.....What happens is if someone calls off they get moved to the top of the list and everyone else gets bumped down. Conversely if someone stays over or picks up an extra shift, they move to bottom of list and everyone else moves up.

Takaharu · Aug 11, 2010, 07:51 AM

Personally, I'd say that you'd be better off just manually cutting and pasting to insert to the top of the list, otherwise you're making a rod for your own back.

As it stands, to move someone to the top of the list, for example, you'd need to change the number next to their name to 3, highlight both cells, cut them then at the top of the table, highlight the top name and the cell adjacent to it, right-click and select "insert cut cells".
Using an automated method, the only way I know of would be to use a macro that will move the contents of the cells. If this is done correctly then the table will do as you want it to but if you're not familiar with the coding that the person has used and you want to, for example, add another number that is assigned in the middle of the table (like 2) then you'd have to seek out the same person (or another person) who could help again.

True, it's not promoting learning on how the query can be addressed, though I'd start slow when it comes to macros (or VB, whichever one is used).

Jitter · Aug 13, 2010, 02:13 AM

Yeah i would go w/ cut and paste myself, but I think its challenging to learn. Basically I got 3 columns w/ a NAME, CODE, and DATE.
The codes range from 1-4.
I need to sort by code, then by date.

The problem is the people w/ the #4 code need the date to be descending to ascending, while the other 3 codes are ascending to descending. The peoples codes change so the number of people w/ a certain code is not static. I didnt know if there was an easy way to sort by code, then seperate the list into 4 lists(one for each code#), and then sort by dates how i wanted. I was also thinking about writing something in VB to sort the date column until the code column was not = to 4. I dont know how much of a project this would be?

Jitter · Aug 13, 2010, 06:34 AM

I think i got it, by recording a macro...First I sort by code, then I filter data...I select "code4" and sort date new to old. Then filter by code 3 and sort by date old to new, then filter by code 2, etc etc.....

Takaharu · Aug 13, 2010, 07:43 AM

There's a date column? That makes all the difference.

Aug 10, 2010, 08:48 PM	#1
Jitter HardwareHeaven Senior Member Join Date: Oct 2003 Posts: 534 Rep Power: 0	Question For Excel Gurus I am trying to make what I guess would be called a rotating list. Basically I want to set something up like this: Name Code# Tom 3 Bill 1 Jim 1 Bob 3 etc...... If you input a code of 1 for a person I want their name/code to goto bottom of list and all other rows to move up. If you input a 3 for a person I want that person/row to goto top of list and all other rows to move down. Is this possible in excel? Thanks Kevin __________________ Sony Vaio PCV-RX670 P4 1.8ghz XP Pro SP2 1 GB PC2100 RAM ATI All in Wonder 9700 pro Audigy 2 ZS Aquamark ~29,500 3dMark03 ~ 5,000

Aug 10, 2010, 11:10 PM	#2
Takaharu I can fart in 7 languages Join Date: Aug 2009 Location: England, UK Posts: 1,838 Rep Power: 107 System Specs	Re: Question For Excel Gurus How about sorting by column after changing a number from 1 to 3 (or vice-versa)? Say column A has the names and column B has the numbers, highlight column B, click sort then Excel should ask if you want to include column A - say yes and you should have column B sorted numerically. __________________ I don't get paid to know the answer, therefore I'm far more likely to give you a straight and honest answer. Mods Rig, Box Mods Rig, Folding details

Aug 11, 2010, 01:18 AM	Thread Starter #3
Jitter HardwareHeaven Senior Member Join Date: Oct 2003 Posts: 534 Rep Power: 0	Re: Question For Excel Gurus well that would kinda work, but this is a mandation list for a nursing home.....What happens is if someone calls off they get moved to the top of the list and everyone else gets bumped down. Conversely if someone stays over or picks up an extra shift, they move to bottom of list and everyone else moves up. __________________ Sony Vaio PCV-RX670 P4 1.8ghz XP Pro SP2 1 GB PC2100 RAM ATI All in Wonder 9700 pro Audigy 2 ZS Aquamark ~29,500 3dMark03 ~ 5,000

Aug 13, 2010, 02:13 AM	Thread Starter #5
Jitter HardwareHeaven Senior Member Join Date: Oct 2003 Posts: 534 Rep Power: 0	Re: Question For Excel Gurus Yeah i would go w/ cut and paste myself, but I think its challenging to learn. Basically I got 3 columns w/ a NAME, CODE, and DATE. The codes range from 1-4. I need to sort by code, then by date. The problem is the people w/ the #4 code need the date to be descending to ascending, while the other 3 codes are ascending to descending. The peoples codes change so the number of people w/ a certain code is not static. I didnt know if there was an easy way to sort by code, then seperate the list into 4 lists(one for each code#), and then sort by dates how i wanted. I was also thinking about writing something in VB to sort the date column until the code column was not = to 4. I dont know how much of a project this would be? __________________ Sony Vaio PCV-RX670 P4 1.8ghz XP Pro SP2 1 GB PC2100 RAM ATI All in Wonder 9700 pro Audigy 2 ZS Aquamark ~29,500 3dMark03 ~ 5,000

Aug 13, 2010, 06:34 AM	Thread Starter #6
Jitter HardwareHeaven Senior Member Join Date: Oct 2003 Posts: 534 Rep Power: 0	Re: Question For Excel Gurus I think i got it, by recording a macro...First I sort by code, then I filter data...I select "code4" and sort date new to old. Then filter by code 3 and sort by date old to new, then filter by code 2, etc etc..... __________________ Sony Vaio PCV-RX670 P4 1.8ghz XP Pro SP2 1 GB PC2100 RAM ATI All in Wonder 9700 pro Audigy 2 ZS Aquamark ~29,500 3dMark03 ~ 5,000

Aug 11, 2010, 07:51 AM	#4
Takaharu I can fart in 7 languages Join Date: Aug 2009 Location: England, UK Posts: 1,838 Rep Power: 107 System Specs	Re: Question For Excel Gurus Personally, I'd say that you'd be better off just manually cutting and pasting to insert to the top of the list, otherwise you're making a rod for your own back. As it stands, to move someone to the top of the list, for example, you'd need to change the number next to their name to 3, highlight both cells, cut them then at the top of the table, highlight the top name and the cell adjacent to it, right-click and select "insert cut cells". Using an automated method, the only way I know of would be to use a macro that will move the contents of the cells. If this is done correctly then the table will do as you want it to but if you're not familiar with the coding that the person has used and you want to, for example, add another number that is assigned in the middle of the table (like 2) then you'd have to seek out the same person (or another person) who could help again. True, it's not promoting learning on how the query can be addressed, though I'd start slow when it comes to macros (or VB, whichever one is used). __________________ I don't get paid to know the answer, therefore I'm far more likely to give you a straight and honest answer. Mods Rig, Box Mods Rig, Folding details

Aug 13, 2010, 07:43 AM	#7
Takaharu I can fart in 7 languages Join Date: Aug 2009 Location: England, UK Posts: 1,838 Rep Power: 107 System Specs	Re: Question For Excel Gurus There's a date column? That makes all the difference. __________________ I don't get paid to know the answer, therefore I'm far more likely to give you a straight and honest answer. Mods Rig, Box Mods Rig, Folding details