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
  • Refurbish
  • 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

  • so you are targeting to solve the reporting bar chart or only the auto index-sort-order thing? | and... mind sharing any sample file/data to replicate the case? – p._phidot_ Jun 11 '19 at 0:59

I decided to just use vlookup when the dropdown for a step is set and have an associated sort order number loaded in the column next to it. This way I control the sort order by having the dropdown lookups on a separate sheet with its associated sort order number. Similar to just two columns but this way I control it all from one sheet and the steps are listed only once for the dropdown

  • glad it solves. ( : – p._phidot_ Jun 11 '19 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.