I have a series of process steps I am tracking in MS Excel. The names of the steps do not sort in the order I want them to appear. Below is a sample of some of the steps:
- Dock Receive
- Load Conveyor
- Quality Check
- Load Software
The data is represented with hundreds of rows each having one of the above steps. Basically, as hundreds of products are received rows are created for each step they pass through the warehouse.
I could just prefix the steps with numbers but that becomes a problem when steps are introduced in the middle or if any are removed. Additionally, for reporting, I don't want to show the numbers on the x or y axis of bar chart reports.
I could have another Excel column before the steps and have a 3rd column which represents the concatenation of the number and the step but this still has the problem for reporting. I need the bar charts to show the bars in the order the steps take place.
I'm wondering if there is a way to somehow have a column somewhere to serve as the index order of the steps which if I change will automatically change the sort order for all rows that use the steps. I know how this would be done if the data were in a database (i.e. a table with each step and an associated sort order number but only the received order of data is used for the chart). Is there some trick to managing a situations like this in Excel? Thanks in advance