Forum Discussion

BDCanuck's avatar
BDCanuck
Copper Contributor
Oct 27, 2022
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 bu...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Oct 27, 2022

    BDCanuck 

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

    You can try this formula.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 27, 2022

BDCanuck 

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

BDCanuck's avatar
BDCanuck
Copper Contributor
Oct 27, 2022
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

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 27, 2022

    BDCanuck 

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

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

    • BDCanuck's avatar
      BDCanuck
      Copper Contributor
      Oct 28, 2022

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

Resources