Sorting data into a neat table is easy with Excel. You don’t need to mess with visual basic or use complicated functions. Let’s say you want to type in the number of customers in various states and have it show up automatically in a tidy alphabetic list of states with their corresponding customers. The result looks something like this:
As you input the number of customers in a yellow box, it automatically pops up listed in order in the blue table.
First, we need to gather states that have customers in their proper order. So we make a table of a hierarchy of states, using these formulas:
But this table repeats states, so we need to know if we should skip to the next state in that table. If the same state shows up, we will put a 1 next to it.
For the blue table, we utilize these two columns of information to list states in order if they have more than 1 customer. In cell E2,E3,E4,E5 type:
Now all we need to do is correspond the amounts inputted with the states we have listed. In cell F2,F3,F4,F5 type:
