Help checking and re-ordering columns

Occasional Contributor

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 orderColumn Headers to be checked from worked filesComparison Check


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 orderColumn Headers to be checked from worked filesComparison CheckMove Column:


So move Column C one place right, D one place right, and B two places left.


Is this possible?

10 Replies
best response confirmed by BDCanuck (Occasional Contributor)



You can try this formula.

move column.JPG 

Thank you! That worked! For anyone else curious, there was a little bit of German left in the formula that @Quadruple_Pawn 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!

Hello again @Quadruple_Pawn! I tried to use the formula with strings of text, (which are what I actually receive as Column Header names). It didn't work. Is there something about this formula that is limited to the alphabet? It seems to work only for strings of three letters or less, with "XFD" being the last string that works.
Sorry if I'm asking dumb questions! I'm pretty new to this!


There are 16384 columns in an Excel sheet. Column XFD is column number 16384 and that is the limitation for the formula.

column XFD.JPG

Ahhh, I see. Is there a different way to do this other than with that formula?
The headers that I am actually using are things like




Do you want to return the column numbers for DVT, DVR and DVX?

column numbers.JPG

@Quadruple_Pawn Not really.


What I'd like is for it to compare the positions of each item in the two lists, and see how far away from each other they are. So if the string "dvr_day_dept_fam_count" is in row 7 on the first list, and row 67 on the second list, I want it to tell me to move that string by 60 positions "up". 


These two column headers lists are just a transposed list of headers. So when it tells me to move the 60 positions, I'll go to my second spreadsheet and move the column 60 positions left, which in our worksheet looks like 60 positions up.


Does that make sense? Is this possible?



You can try formula in the attached file.


You're a genius! A God among us lowly folks! Thanks!!