Forum Discussion
Solved
Help checking and re-ordering columns
Hello all,
I have data that comes to me in a long line of columns that are regularly switched around. Every week I need to move the columns to make the data conform to a spreadsheet that has a bunch of formulas.
Is there a formula I can use to make Excel tell me where to move the column?
What I have now outputs something like this:
| Column Headers in correct order | Column Headers to be checked from worked files | Comparison Check |
| A | A | Good |
| B | C | Bad |
| C | D | Bad |
| D | B | Bad |
| E | E | Good |
What I'd like is something where a formula told me how far to move a column. It would output something like this:
| Column Headers in correct order | Column Headers to be checked from worked files | Comparison Check | Move Column: |
| A | A | Good | 0 |
| B | C | Bad | 1 |
| C | D | Bad | 1 |
| D | B | Bad | -2 |
| E | E | Good | 0 |
So move Column C one place right, D one place right, and B two places left.
Is this possible?
- Thank you! That worked! For anyone else curious, there was a little bit of German left in the formula that OliverScheurich suggested. The formula for English Excel is =CELL("Col",INDIRECT(B2&"1"))-CELL("Col",INDIRECT(A2&"1"))
You are welcome! Thank you for the correction!
