Help checking and re-ordering columns

[BDCanuck]

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?

[#2 · Accepted solution]

@BDCanuck 

=CELL("Spalte",INDIRECT(B2&"1"))-CELL("Spalte",INDIRECT(A2&"1"))

You can try this formula.

 

[#3]

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"))

[#4]

@BDCanuck 

You are welcome! Thank you for the correction!

[#5]

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!

[#6]

@BDCanuck 

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

[#7]

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

dvt_day_dept_fam_count
dvr_day_dept_fam
dvr_day_dept_fam_count
dvt_day_dept_count
dvx_day_family

[#8]

@BDCanuck 

=CELL("col",INDIRECT(LEFT(A1,3)&"1"))

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

[#9]

@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?

[#10]

@BDCanuck 

=MATCH(A10,$1:$1,0)-MATCH(A10,List2!$1:$1,0)

You can try formula in the attached file.

[#11]

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