Forum Discussion

ESKARDA's avatar
ESKARDA
Copper Contributor
Sep 26, 2019

Is there a formula to drag data listed in rows into a single column?

Is there a formula to drag data listed in rows into a single column? I would rather not copy and transpose paste all 2304 rates. I have a matrix of state to state rates in the contract-state...ta...
Excel formulas
ESKARDA's avatar
ESKARDA
Copper Contributor
Sep 27, 2019

IngeborgHawighorst 

My apologies. I attached the correct spreadsheet.   

In the Contract-State-R tab is a state to state rate matrix. The Origin states are in Column A and the destination states are on row 2. I am looking for the most efficient way to get the rates into Column B (next to the corresponding ST-ST in Column A) 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 27, 2019

ESKARDA 

If I understood your logic correctly

For such source matrix

convert it to two columns (J and K) could be used

in J3

=INDEX($B:$B, INT( (ROW()-ROW($B$2)-1)/$J$1)+ROW($B$2)+1) &
   "-" &
   INDEX($C$2:$H$2,MOD(ROW()-ROW($B$2)-1,$J$1)+1)

in K3

=INDEX(
   INDEX(C:H,
      INT( (ROW()-ROW($B$2)-1)/$J$1)+
      ROW($B$2)+1,0
   ),
   MOD(ROW()-ROW($B$2)-1,$J$1)+1
)

and need to be adjusted to your actual ranges and sheets.

  • ESKARDA's avatar
    ESKARDA
    Copper Contributor
    Oct 02, 2019

    You are a life saver thank you so muchSergeiBaklan 

Resources