SOLVED

Help checking and re-ordering columns

Occasional Contributor

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?

10 Replies
best response confirmed by BDCanuck (Occasional Contributor)
Solution

Re: Help checking and re-ordering columns

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

You can try this formula.

Re: Help checking and re-ordering columns

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

Re: Help checking and re-ordering columns

You are welcome! Thank you for the correction!

Re: Help checking and re-ordering columns

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!

Re: Help checking and re-ordering columns

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

Re: Help checking and re-ordering columns

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

Re: Help checking and re-ordering columns

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

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

Re: Help checking and re-ordering columns

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?

Re: Help checking and re-ordering columns

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

You can try formula in the attached file.

Re: Help checking and re-ordering columns

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