Sort and Filter Help

[djlmll06]

I have a spreadsheet that lists various projects with several columns. The problem is if I do a sort on the project name it doesn't keep the project number with the sort. For example, if I sort by column B (Project) I want column A (No.) to stay with the project but instead column A (No.) always stays in numerical order. How do I get cell A for each row to stay with the project?


<TABLE style="WIDTH: 474pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=631 border=0><COLGROUP><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 60pt" height=80><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 19pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 60pt; BACKGROUND-COLOR: #aebad5" width=25 height=80>No.</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=144>Project</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=92>Administrator Responsible</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=70>Project Leader</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=81>Project Manager</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=71>Project Assigned Date</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 51pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=68>Project Start Date</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #aebad5; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #AEBAD5 none" width=80>Project Completion Due</TD></TR><TR style="HEIGHT: 96pt; mso-height-source: userset" height=128><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 96pt; BACKGROUND-COLOR: transparent" height=128>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=144>IT Product Comparison </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=92>Mouse</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=70>Donald</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=81>Smith</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=71>1/26/11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 51pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=68>1/26/11</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=80>4/30/11</TD></TR><TR style="HEIGHT: 51pt; mso-height-source: userset" height=68><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: transparent" height=68>2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=144> Operating Protocol – Commercial </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=92>Duck</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=70>Donald</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=81>West</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>4/1/11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=68>3/3/11</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>5/1/11</TD></TR><TR style="HEIGHT: 60pt" height=80><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 60pt; BACKGROUND-COLOR: transparent" height=80>3</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=144>Medical Home Reassessment</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=92>East</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=70>TBD</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=81>TBD</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=71>1/20/11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 51pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=68>2/1/11</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #eef0f6; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #EEF0F6 none" width=80>5/1/11</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" height=40>4</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=144>Inpatient Physician Advisor Program</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=92>North</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=70>North</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: auto; mso-background-source: auto" width=81>Donald</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71>11/4/10</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=68>3/15/11</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>5/1/11</TD></TR></TBODY></TABLE>

 

[mtskv5]

change column A to a value if it is a formula. Then select the entire range of your data and sort. This is maintain the Project Number.

Side Note - Visio is a good product for Project Management tasks.

 

[djlmll06]

I cleared the formula, put in values 1 - 4 in A. Clicked on the filter button in column B and column A stays 1-4 instead of changing.

 

[mtskv5]

Not quite sure. I'm testing it with your data set and it is giving me the numbers that originally went with that row. When you set the filters, did you select the entire range?

 

[djlmll06]

When I received this spreadsheet from the creator, columns B - Q have the filter on the header but column A doesn't. I remove the filter on B-Q then highlight A-Q but then I can't click on the filter funnel. Not sure why though.

 

[mtskv5]

Your Project No. is not part of your "Table" in Excel. Set the table as a range and then apply a new set of filters to your data.